VBA（Excel）：基于多个搜索条件查找不循环 [英] VBA (Excel): Find Based on Multiple Search Criteria Without Looping

问题描述

我有一个大数据表，我想根据3组标准在VBA中进行搜索。每一行条目都可以被认为是唯一的。由于要求，纸张/数据本身的格式不能更改。 （我已经看过有关相关问题的几篇文章，但还没有找到一个可行的解决方案。）

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.)

起初我使用经典的VBA 查找一个循环中的方法：

At first I used the classic VBA find method in a loop:

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 match method with index. So I unsuccessfully tried many variations of that such as:

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

和

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 ))

然后我尝试使用 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.

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

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?

推荐答案

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

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

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

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

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

这篇关于VBA（Excel）：基于多个搜索条件查找不循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！