I am writing a macro for an excel document. it takes a string from a list of strings in column "R" and searches ALL through column "J" to find the string. If it finds the string, it serches the columns "L" and "N" to find out if the numbers are the same against that string on "J". if it does, It highlights the corresponding string at Column "R".

This is the code I wrote:

Public Sub FindApproved()
    Dim rnge, rnge1 As Range
    Dim selectedRowNum As Long
    Dim lAmt, nAmt As Currency
    Dim referenceNum As String
    Dim paymentStatus As String
    Dim num As String
    Dim isTransactionApproved As Boolean
    isTransactionApproved = False
    For Each rnge In Selection.Rows
        selectedRowNum = rnge.Row
        referenceNum = rnge.Columns("R").Value
        For Each rnge1 In Selection.Rows
            Dim start As Characters
            Dim charCount As Integer
            If (referenceNum = Mid$((rnge1.Columns("J").Value), start =   R, charCount = 7)) Then
               num = referenceNum
            End If
        Next rnge1
        If (num = referenceNum) Then
            If (rnge.Columns("L").Value = lAmt And rnge.Columns("N").Value = _
                  "lAmt" And rnge.Columns("K").Value = "Approved") Then
                       isTransactionApproved = True
                       Range(num).Interior.ColorIndex = 36
            End If
        End If
    Next rnge
End Sub

But when I stepInto it, it gives me a runtime error("Run-time error '91': Object variable or with block variable not set"). PLEASE HELP ME QUICKLY BECAUSE I AM TO DELIVER IT LATER TODAY. THANKS TO THAT BLESSED PERSON IN ADVANCE FOR YOUR HELP.

9 Years
Discussion Span
Last Post by Jupiter 2

it gives me a runtime error("Run-time error '91': Object variable or with block variable not set").

It doesn't look like you instantiated your ranges. Don't know.

Set rnge = new Range

Also, you should be careful in your declaration of variables. VB6 is not like C.

Dim rnge, intI as Integer,  road as Range

The variable rnge will be set to a variant type, intI as an Integer, and road as a Range.


For Each rnge In Selection.Rows

You haven't selected a row unless it is selected by you.

Try for each Cell in selection

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.