使用 Excel VBA 在工作簿中查找所有匹配项 [英] Find all matches in workbook using Excel VBA

问题描述

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

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

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

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 Find function 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.

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

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

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

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?

更新

虽然下面的答案会改进我的方法，但我最终选择了一些稍微不同的东西，因为我需要一遍又一遍地进行多个查询.

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.

基本上只是做一次初始扫描，将所有内容存储在一个可管理的结构中，然后查询可以在O(1)时间内完成的结构

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

推荐答案

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

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!".

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

这篇关于使用 Excel VBA 在工作簿中查找所有匹配项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！