从范围的底部开始，在vba内执行查找 [英] Perform a FIND, within vba, from the bottom of a range up

问题描述

下午好，

Find 是否可以从一个范围的底部开始， ？我想要的是我的代码首先找到一个记录号码，将位于主列表。一旦找到记录号码，我希望它将交易名称（记录号码的一个 offset ）分配给一个变量，然后在主列表中搜索第一个交易那个同名的

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

如果反向查找不可能，那么一个vlookup的工作？有可能通过创建一个范围从记录数上面的第一行开始，并有vlookup找到最后的发生？

任何帮助表示赞赏。

  PendingBRow = ThisWorkbook.Sheets（PendingLog）。Range（A65000）。End（xlUp）.Row 
 $ MasterBow = 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（ （B2：B& MasterBRow）
 Set c = .Find（PendingRecNum，LookIn：= xlValues）
如果Not c不是Nothing那么
 firstRow = c。行 -  1 
 
 O = 1 
直到firstRow = O 
 LastWorkedBy = ThisWorkbook.Sheets（MasterLog）。Range（E& firstRow）.off set（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）如果PendingDealName = dealName然后
 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 
   > FIND（） 底部   ：
 $ b $ pre $ code $ Sub FindFromTheBottom 
 Set a = Range（A：A）。Find（Test ，之后：=单元格（1,1）， searchdirection：= xlPrevious）
 MsgBox a.Address（0，0）
 End Sub 
  

Good Afternoon,

Is it possible for Find to start from the bottom of a range and work up? What I would like is my code to first find a record number that will be 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 same name.

Currently, I have code that finds the record number, assigns the deal name to a variable and then loop's 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 1 row above the record number to the top and have vlookup find the last occurance?

Any help is appreciated.

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

解决方案

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

这篇关于从范围的底部开始，在vba内执行查找的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！