excel - 在 VBA 中从范围底部向上执行 FIND

Question

Find 是否可以从范围的底部开始向上查找？

我希望我的代码首先找到位于主列表上的记录号。一旦它找到记录编号，我希望它将该交易名称(记录编号的偏移量)分配给变量，然后在主列表中搜索具有该名称的第一个交易。

我有代码可以查找记录编号，将交易名称分配给变量，然后循环每个单元格直到找到匹配项。虽然这种方法有效，但循环处理时间明显慢于查找处理时间，我正在寻找最快的解决方案。

如果反向查找不可行，vlookup 会起作用吗？可能是通过创建一个从记录号上方一行开始到顶部的范围，并让 vlookup 找到最后一次出现的位置？

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

Answer 1

这将从底部FIND():

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