使用 Excel VBA 在工作簿中查找所有匹配项

Find all matches in workbook using Excel VBA

提问by MZimmerman6

I am trying to write a VBAroutine that will take a string, search a given Excel workbook, and return to me all possible matches.

我正在尝试编写一个VBA例程，该例程将接受一个字符串，搜索给定的 Excel 工作簿，并将所有可能的匹配项返回给我。

I currently have an implementation that works, but it is extremely slow as it is a double for loop. Of course the built in Excel Findfunction is "optimized" to find a single match, but I would like it to return an array of initial matches that I can then apply further methods to.

我目前有一个有效的实现，但它非常慢，因为它是一个双循环。当然，内置的 ExcelFind函数经过“优化”以找到单个匹配项，但我希望它返回一组初始匹配项，然后我可以对其应用进一步的方法。

I will post some pseudocode of what I have already

我将发布一些我已经拥有的伪代码

For all sheets in workbook
    For all used rows in worksheet
        If cell matches search string
            do some stuff
        end
    end
end

As previously stated, this double for loop makes things run very slowly, so I am looking to get rid of this if possible. Any suggestions?

如前所述，这个双重 for 循环使事情运行得非常缓慢，所以如果可能的话，我希望摆脱这种情况。有什么建议？

UPDATE

更新

While the below answers would have improved my method, I ended up going with something slightly different as I needed to do multiple queries over and over.

虽然以下答案会改进我的方法，但我最终采用了略有不同的方法，因为我需要一遍又一遍地进行多个查询。

I instead decided to loop through all rows in my document and create a dictionary containing a key for each unique row. The value this points to will then be a list of possible matches, so that when I query later, I can simply just check if it exists, and if so, just get a quick list of matches.

我决定遍历文档中的所有行并创建一个字典，其中包含每个唯一行的键。这个指向的值将是一个可能匹配的列表，这样当我稍后查询时，我可以简单地检查它是否存在，如果存在，只需快速获取匹配列表。

Basically just doing one initial sweep to store everything in a manageable structure, and then query that structure which can be done in O(1)time

基本上只是在做一个初始扫描存储一切都在一个可管理的结构，然后查询该结构可以在完成O(1)时间

回答by Jerome Montino

Using the Range.Find method, as pointed out above, along with a loop for each worksheet in the workbook, is the fastest way to do this. The following, for example, locates the string "Question?" in each worksheet and replaces it with the string "Answered!".

如上所述，使用 Range.Find 方法以及工作簿中每个工作表的循环是执行此操作的最快方法。例如，下面的代码定位字符串“Question?” 在每个工作表中，并将其替换为字符串“Answered!”。

Sub FindAndExecute()

Dim Sh As Worksheet
Dim Loc As Range

For Each Sh In ThisWorkbook.Worksheets
    With Sh.UsedRange
        Set Loc = .Cells.Find(What:="Question?")
        If Not Loc Is Nothing Then
            Do Until Loc Is Nothing
                Loc.Value = "Answered!"
                Set Loc = .FindNext(Loc)
            Loop
        End If
    End With
    Set Loc = Nothing
Next

End Sub

回答by cyberponk

Based on Ahmed's answer, after some cleaning up and generalization, including the other "Find" parameters, so we can use this function in any situation:

基于 Ahmed 的回答，经过一些清理和概括，包括其他“查找”参数，因此我们可以在任何情况下使用此函数：

'Uses Range.Find to get a range of all find results within a worksheet
' Same as Find All from search dialog box
'
Function FindAll(rng As Range, What As Variant, Optional LookIn As XlFindLookIn = xlValues, Optional LookAt As XlLookAt = xlWhole, Optional SearchOrder As XlSearchOrder = xlByColumns, Optional SearchDirection As XlSearchDirection = xlNext, Optional MatchCase As Boolean = False, Optional MatchByte As Boolean = False, Optional SearchFormat As Boolean = False) As Range
    Dim SearchResult As Range
    Dim firstMatch As String
    With rng
        Set SearchResult = .Find(What, , LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
        If Not SearchResult Is Nothing Then
            firstMatch = SearchResult.Address
            Do
                If FindAll Is Nothing Then
                    Set FindAll = SearchResult
                Else
                    Set FindAll = Union(FindAll, SearchResult)
                End If
                Set SearchResult = .FindNext(SearchResult)
            Loop While Not SearchResult Is Nothing And SearchResult.Address <> firstMatch
        End If
    End With
End Function

回答by B Hart

Function GetSearchArray(strSearch)
Dim strResults As String
Dim SHT As Worksheet
Dim rFND As Range
Dim sFirstAddress
For Each SHT In ThisWorkbook.Worksheets
    Set rFND = Nothing
    With SHT.UsedRange
        Set rFND = .Cells.Find(What:=strSearch, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlRows, SearchDirection:=xlNext, MatchCase:=False)
        If Not rFND Is Nothing Then
            sFirstAddress = rFND.Address
            Do
                If strResults = vbNullString Then
                    strResults = "Worksheet(" & SHT.Index & ").Range(" & Chr(34) & rFND.Address & Chr(34) & ")"
                Else
                    strResults = strResults & "|" & "Worksheet(" & SHT.Index & ").Range(" & Chr(34) & rFND.Address & Chr(34) & ")"
                End If
                Set rFND = .FindNext(rFND)
            Loop While Not rFND Is Nothing And rFND.Address <> sFirstAddress
        End If
    End With
Next
If strResults = vbNullString Then
    GetSearchArray = Null
ElseIf InStr(1, strResults, "|", 1) = 0 Then
    GetSearchArray = Array(strResults)
Else
    GetSearchArray = Split(strResults, "|")
End If
End Function

Sub test2()
For Each X In GetSearchArray("1")
    Debug.Print X
Next
End Sub

Careful when doing a Find Loop that you don't get yourself into an infinite loop... Reference the first found cell address and compare after each "FindNext" statement to make sure it hasn't returned back to the first initially found cell.

在执行查找循环时要小心，不要让自己陷入无限循环......引用第一个找到的单元格地址并在每个“FindNext”语句之后进行比较，以确保它没有返回到最初找到的第一个单元格。

回答by Christian Fries

You may use the Range.Find method:

您可以使用 Range.Find 方法：

http://msdn.microsoft.com/en-us/library/office/ff839746.aspx

http://msdn.microsoft.com/en-us/library/office/ff839746.aspx

This will get you the first cell which contains the search string. By repeating this with setting the "After" argument to the next cell you will get all other occurrences until you are back at the first occurrence.

这将为您提供包含搜索字符串的第一个单元格。通过将“After”参数设置为下一个单元格来重复此操作，您将获得所有其他事件，直到您回到第一次出现。

This will likely be much faster.

这可能会快得多。

回答by Ahmed Abdelhameed

Based on the idea of B Hart's answer, here's my version of a function that searches for a value in a range, and returns all found ranges (cells):

基于 B Hart 的回答的想法，这是我的一个函数版本，它在一个范围内搜索一个值，并返回所有找到的范围（单元格）：

Function FindAll(ByVal rng As Range, ByVal searchTxt As String) As Range
    Dim foundCell As Range
    Dim firstAddress
    Dim rResult As Range
    With rng
        Set foundCell = .Find(What:=searchTxt, _
                              After:=.Cells(.Cells.Count), _
                              LookIn:=xlValues, _
                              LookAt:=xlWhole, _
                              SearchOrder:=xlByRows, _
                              SearchDirection:=xlNext, _
                              MatchCase:=False)
        If Not foundCell Is Nothing Then
            firstAddress = foundCell.Address
            Do
                If rResult Is Nothing Then
                    Set rResult = foundCell
                Else
                    Set rResult = Union(rResult, foundCell)
                End If
                Set foundCell = .FindNext(foundCell)
            Loop While Not foundCell Is Nothing And foundCell.Address <> firstAddress
        End If
    End With

    Set FindAll = rResult
End Function

To search for a value in the whole workbook:

要在整个工作簿中搜索值：

Dim wSh As Worksheet
Dim foundCells As Range
For Each wSh In ThisWorkbook.Worksheets
    Set foundCells = FindAll(wSh.UsedRange, "YourSearchString")
    If Not foundCells Is Nothing Then
        Debug.Print ("Results in sheet '" & wSh.Name & "':")
        Dim cell As Range
        For Each cell In foundCells
            Debug.Print ("The value has been found in cell: " & cell.Address)
        Next
    End If
Next

回答by Nandan A

In my Scenario, i have to look for the value in column A and need to find out the matches in column B. So i have created a for loop, inside it will look up into the entire column A and get the exact match from Column B.

在我的场景中，我必须查找 A 列中的值并需要找出 B 列中的匹配项。所以我创建了一个 for 循环，在它内部将查找整个 A 列并从 Column 中获取精确匹配B.

Sub Type3()

Dim loc As String
Dim k As Integer
Dim i As Integer
Dim j As Integer
Dim findpage As String
Dim methodlist As String    

findpage = "benefits" 'We can change this values as  dynamic
k = Sheet1.Range("A1048576").End(xlUp).Row

For i = 1 To k
         loc = Sheet1.Cells(i, 1).Value           
        If StrComp(findpage, loc) = 0 Then                   
                 method = Cells(i, 2).Value
                 methodlist = methodlist + "," + method   'We can use string array as well                                   
        End If         
Next i            
End Sub

回答by Nivedita Tanya

Below code avoids creating infinite loop. Assume XYZ is the string which we are looking for in the workbook.

下面的代码避免了创建无限循环。假设 XYZ 是我们在工作簿中寻找的字符串。

   Private Sub CommandButton1_Click()
   Dim Sh As Worksheet, myCounter
   Dim Loc As Range

   For Each Sh In ThisWorkbook.Worksheets
   With Sh.UsedRange
   Set Loc = .Cells.Find(What:="XYZ")

    If Not Loc Is Nothing Then

           MsgBox ("Value is found  in " & Sh.Name)
           myCounter = 1
            Set Loc = .FindNext(Loc)

    End If
End With
Next
If myCounter = 0 Then
MsgBox ("Value not present in this worrkbook")
End If

End Sub

回答by Gerhard Powell

You can read the data into an array. From there you can do the match in memory, instead of reading one cell at a time.

您可以将数据读入数组。从那里你可以在内存中进行匹配，而不是一次读取一个单元格。

Pass cell contents into VBA Array

将单元格内容传递到 VBA 数组