在 VBA 中，从范围的底部向上执行 FIND
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22464631/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Perform a FIND, within VBA, from the bottom of a range up
提问by John W
Is it possible for Findto start from the bottom of a range and work up?
是否有可能Find从一个范围的底部开始并向上工作？
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 offsetof the record number, to a variable and then search up the master list for the first deal with that name.
我希望我的代码首先找到位于主列表中的记录号。一旦找到记录号，我希望它将该交易名称（offset记录号的一个）分配给一个变量，然后在主列表中搜索具有该名称的第一笔交易。
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?
如果反向查找是不可能的，那么 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
回答by Gary's Student
This will FIND()from the bottom:
这将FIND（）从底部：
Sub FindFromTheBottom()
Set a = Range("A:A").Find("Test", after:=Cells(1, 1), searchdirection:=xlPrevious)
MsgBox a.Address(0, 0)
End Sub
回答by Fen
the after cell specified has to be within the search range; if you remove after:=, then active cell is taken as the after cell.
指定的后单元格必须在搜索范围内；如果删除 after:=，则将活动单元格作为后单元格。
