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

Question

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

我目前有一个可行的实现，但是它非常慢，因为它是double for循环。当然，内置的Excel Find函数经过“优化”以查找单个匹配项，但是我希望它返回一个初始匹配项数组，然后可以将其应用于其他方法。

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

For all sheets in workbook

    For all used rows in worksheet

        If cell matches search string

            do some stuff

        end

    end

end

如前所述，此double for循环使事情运行非常缓慢，因此，我希望尽可能消除这种情况。有什么建议么？

更新

尽管下面的答案可以改善我的方法，但由于需要一遍又一遍地进行多次查询，最终我得到了些许不同。

相反，我决定遍历文档中的所有行，并为每个唯一行创建一个包含键的字典。然后，此指向的值将是可能匹配项的列表，以便稍后查询时，我可以仅检查它是否存在，如果存在，则可以快速获取匹配项列表。

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

Answer 1

如上所述，使用Range.Find方法以及工作簿中每个工作表的循环是最快的方法。例如，以下代码查找字符串“ Question？”。在每个工作表中，并将其替换为字符串“ Answered！”。Sub FindAndExecute()Dim Sh As WorksheetDim Loc As RangeFor 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 = NothingNextEnd Sub

Answer 2

Function GetSearchArray(strSearch)Dim strResults As StringDim SHT As WorksheetDim rFND As RangeDim sFirstAddressFor Each SHT In ThisWorkbook.Worksheets&nbsp; &nbsp; Set rFND = Nothing&nbsp; &nbsp; With SHT.UsedRange&nbsp; &nbsp; &nbsp; &nbsp; Set rFND = .Cells.Find(What:=strSearch, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlRows, SearchDirection:=xlNext, MatchCase:=False)&nbsp; &nbsp; &nbsp; &nbsp; If Not rFND Is Nothing Then&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sFirstAddress = rFND.Address&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Do&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; If strResults = vbNullString Then&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; strResults = "Worksheet(" & SHT.Index & ").Range(" & Chr(34) & rFND.Address & Chr(34) & ")"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Else&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; strResults = strResults & "|" & "Worksheet(" & SHT.Index & ").Range(" & Chr(34) & rFND.Address & Chr(34) & ")"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; End If&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Set rFND = .FindNext(rFND)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Loop While Not rFND Is Nothing And rFND.Address <> sFirstAddress&nbsp; &nbsp; &nbsp; &nbsp; End If&nbsp; &nbsp; End WithNextIf strResults = vbNullString Then&nbsp; &nbsp; GetSearchArray = NullElseIf InStr(1, strResults, "|", 1) = 0 Then&nbsp; &nbsp; GetSearchArray = Array(strResults)Else&nbsp; &nbsp; GetSearchArray = Split(strResults, "|")End IfEnd FunctionSub test2()For Each X In GetSearchArray("1")&nbsp; &nbsp; Debug.Print XNextEnd Sub在执行“查找循环”时要小心，不要使自己陷入无限循环...请引用第一个找到的单元格地址，并在每个“ FindNext”语句之后进行比较，以确保它没有返回到最初找到的第一个单元格。