VBA (Excel)：基于不循环的多个搜索条件查找

VBA (Excel): Find Based on Multiple Search Criteria Without Looping

提问by user1205577

I have a large data sheet that I want to search in VBA based on 3 sets of criteria. Each row entry can be assumed to be unique. The format of the sheet/data itself cannot be changed due to requirements. (I've seen several posts on related questions but haven't found a working solution for this yet.)

我有一个大型数据表，我想根据 3 组标准在 VBA 中进行搜索。可以假设每个行条目都是唯一的。表格/数据本身的格式不能因要求而改变。（我已经看过几篇关于相关问题的帖子，但还没有找到一个可行的解决方案。）

At first I used the classic VBA findmethod in a loop:

起初我在循环中使用了经典的 VBA find方法：

Set foundItem = itemRange.Find(What:=itemName, Lookin:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows)
If Not foundItem Is Nothing Then
    firstMatchAddr = foundItem.Address
    Do
        ' *Check the other fields in this row for a match and exit if found*
        Set foundItem = itemRange.FindNext(foundItem)
    Loop While foundItem.Address <> firstMatchAddr  And Not foundItem Is Nothing
End If

But because this needs to be called a number of times on large sets of data, the speed of this was no good.

但是因为这需要在大量数据上多次调用，所以速度并不好。

I did some searching and found that I could use the matchmethod with index. So I unsuccessfully tried many variations of that such as:

我做了一些搜索，发现我可以将match方法与index 一起使用。因此，我尝试了许多变体，但没有成功，例如：

result = Evaluate("=MATCH(1, (""" & criteria1Name & """=A2:A" & lastRow & ")*(""" & criteria2Name & """=B2:B" & lastRow & ")*(""" & criteria3Name & """=C2:C" & lastRow & "), 0)")

And

和

result = Application.WorksheetFunction.Index(resultRange, Application.WorksheetFunction.Match((criteria1Name = criteria1Range)*(criteria2Name = criteria2Range)*(criteria3Name = criteria3Range))

And

和

result = Application.WorksheetFunction.Index(resultRange, Application.WorksheetFunction.Match((criteria1Range=criteria1Name )*(criteria2Range=criteria2Name )*(criteria3Range=criteria3Name ))

Then I tried using AutoFilterto sort:

然后我尝试使用AutoFilter进行排序：

.Range(.Cells(1,1), .Cells(lastRow, lastCol)).AutoFilter Field:=1, Criteria1:="=" & criteria1Name
.Range(.Cells(1,1), .Cells(lastRow, lastCol)).AutoFilter Field:=2, Criteria1:="=" & criteria2Name
.Range(.Cells(1,1), .Cells(lastRow, lastCol)).AutoFilter Field:=3, Criteria1:="=" & criteria3Name

But because one of the sorting columns contains dates, I had issues getting AutoFilter to work properly.

但是因为其中一个排序列包含日期，所以我在让 AutoFilter 正常工作时遇到了问题。

My question is, how can I search through columns in Excel VBA based on multiple criteria, without looping, returning either the row number or the value in the cell of that row that I am interested in?

我的问题是，如何根据多个条件搜索 Excel VBA 中的列，而不循环，返回行号或我感兴趣的那一行的单元格中的值？

采纳答案by tigeravatar

You could use an Advanced Filter. Put the column headers in a separate part of the sheet (or a different sheet altogether). Under those column headers, put the criteria you're looking for in each column. Then name that range (including the headers) something like "Criteria". Then the macro becomes:

您可以使用高级过滤器。将列标题放在工作表的单独部分（或完全不同的工作表）。在这些列标题下，将您要查找的条件放在每列中。然后将该范围（包括标题）命名为“标准”。然后宏变成：

Sub Macro1()

    Sheets("Sheet1").Range("A1").CurrentRegion.AdvancedFilter xlFilterInPlace, Range("Criteria")

End Sub

As a follow up to my comment below, to have the VBA create the criteria range behind the scenes:

作为下面我的评论的后续，让 VBA 在幕后创建标准范围：

Sub Macro1()

    'Code up here that defines the criteria

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    With Sheets.Add
        'Create the advanced filter criteria range
        .Range("A1") = "HeaderA"
        .Range("B1") = "HeaderB"
        .Range("C1") = "HeaderC"
        .Range("A2") = criteria1Name
        .Range("B2") = criteria2Name
        .Range("C2") = criteria3Name

        'Alternately, to save space:
        '.Range("A1:C1").Value = Array("HeaderA", "HeaderB", "HeaderC")
        '.Range("A2:C2").Value = Array(criteria1Name, criteria2Name, criteria3Name)

        'Then perform the advanced filter
        Sheets("Sheet1").Range("A1").CurrentRegion.AdvancedFilter xlFilterInPlace, .Range("A1:C2")

        'Remove behind the scenes sheet now that the filter is completed
        .Delete
    End With

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

回答by brettdj

You can use EVALUATEfor multiple criteria like so to return the row numbers of mathcing values. This uses the same approach as Is it possible to fill an array with row numbers which match a certain criteria without looping?

您可以EVALUATE像这样使用多个条件来返回数学值的行号。这使用与是否可以使用符合特定条件而不循环的行号填充数组的方法相同的方法？

• It searches 50000 rows to match
  • the first four letters in column A matches fred
  • the date in B to be greater than 1/1/2001
  • applein column 5
• Any rows matching these criteria are retuned as row numbers in x

• 它搜索 50000 行以匹配
  • A 列中的前四个字母匹配 fred
  • B 中的日期大于 1/1/2001
  • apple在第 5 列
• 任何符合这些条件的行都被重新调整为行号 x

(rows 1 and 5 in picture below)

（下图中的第 1 行和第 5 行）

code

代码

Sub GetEm2()
x = Filter(Application.Transpose(Application.Evaluate("=IF((LEFT(A1:A10000,4)=""fred"")*(B1:B10000>date(2001,1,1))*(C1:C10000=""apple""),ROW(A1:A10000),""x"")")), "x", False)
End Sub

Application.Transposeis limited to 65536 cells, so a longer range needs to be "chunked" into pieces.

Application.Transpose限制为 65536 个单元格，因此需要将更长的范围“分块”成碎片。