Perform a FIND, within VBA, from the bottom of a range up

Question

Is it possible for Find to start from the bottom of a range and work up?

I would like my code to first find a record number located on a master list. Once it finds the record number I want it to assign that deals name, an offset of the record number, to a variable and then search up the master list for the first deal with that name.

I have code that finds the record number, assigns the deal name to a variable and then loops up each cell until it finds a match. Although this way works, the loop processing time is significantly slower than the find processing time and I am searching for the fastest solution.

If reverse find is not a possibility, would a vlookup work? Possibly by, creating a range beginning one row above the record number to the top and have vlookup find the last occurrence?

PendingBRow = ThisWorkbook.Sheets("PendingLog").Range("A65000").End(xlUp).Row
MasterBRow = ThisWorkbook.Sheets("MasterLog").Range("A65000").End(xlUp).Row

For D = 2 To PendingBRow
    With ThisWorkbook.Sheets("PendingLog").Range("A" & D)
        PendingRecNum = .Value
        PendingDealName = .offset(0, 3).Value
        PDLenght = Len(PendingDealName) - 4
        PendingDealName = Left(PendingDealName, PDLenght)
        PendingDealName = UCase(PendingDealName)
        PendingDealName = Trim(PendingDealName)
    End With

    With ThisWorkbook.Sheets("MasterLog").Range("B2:B" & MasterBRow)
        Set c = .Find(PendingRecNum, LookIn:=xlValues)
        If Not c Is Nothing Then
            firstRow = c.Row - 1

            O = 1
            Do Until firstRow = O
                LastWorkedBy = ThisWorkbook.Sheets("MasterLog").Range("E" & firstRow).offset(0, 20)
                MasterRecNum = ThisWorkbook.Sheets("MasterLog").Range("E" & firstRow).offset(0, -3).Value
                dealName = ThisWorkbook.Sheets("MasterLog").Range("E" & firstRow).Value
                dealName = Left(dealName, 10)
                dealName = UCase(dealName)
                dealName = Trim(dealName)

                If PendingDealName = dealName Then
                    MasterLastWorkedBy = LastWorkedBy
                    ThisWorkbook.Sheets("PendingLog").Range("A" & D).offset(0, 19).Value = MasterLastWorkedBy

                    firstRow = O
                Else
                    firstRow = firstRow - 1
                End If

            Loop

        End If
    End With

Next D

Solution 1

This will FIND() from the bottom:

Sub FindFromTheBottom()
    Set a = Range("A:A").Find("Test", after:=Cells(1, 1), searchdirection:=xlPrevious)
    MsgBox a.Address(0, 0)
End Sub

Solution 2

the after cell specified has to be within the search range; if you remove after:=, then active cell is taken as the after cell.

Comments

[John W]

Thank you for your answer. That looks like what I am looking for. However, can you show how to adjust the code to limit the range it searches. I tried changing the range to be A2:A8 but it returned a Mismatch Error. In my case, "Test is a variable located in Column A, When I do the search I don't want it to include the variable in my search range. Only include the cells above it. So for instance, if "Test" was my variable and it was located in cell A9 I would want my range to be A2:A8. Can you expand your answer to account for this?

[John W]

I ended up revmoving the "after:=" portion and was able to specifiy the range. It will perfectly after that. Thank you for the code.

[gimmegimme]

I'd like to add, make sure that After argument is not specified, like the following: , After:=searchRng(searchRng.Cells.Count)