4

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
Community
  • 1
  • 1
John W
  • 191
  • 2
  • 6
  • 14
  • Hello! Please include the relevant code that you are currently using, as well as code / formulas for any solutions you've tried already. also include with the solutions you tried already why they did not work (did you get errors? did you get bad results? and if bad results, why were they bad?) This will help us diagnose the problem and help you find a better solution :) – user1759942 Mar 17 '14 at 20:36
  • @user1759942 Thank you for your response and I will post my code shortly. But, just to be clear, the code I have functions correctly. I am mainly curious if Reverse Find is even possible and if not does my vlookup suggestion seem like a reasonable alternative. – John W Mar 17 '14 at 20:42

2 Answers2

20

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
Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • 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 Mar 18 '14 at 11:58
  • 1
    I ended up revmoving the "after:=" portion and was able to specifiy the range. It will perfectly after that. Thank you for the code. – John W Mar 20 '14 at 12:19
  • I'd like to add, make sure that After argument is not specified, like the following: , After:=searchRng(searchRng.Cells.Count) – gimmegimme Aug 30 '18 at 22:35
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.

Fen
  • 31
  • 3