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

问题描述

我正在尝试写一个 c> 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 查找函数是优化来查找单个匹配，但我希望返回一个初始匹配数组，然后我可以应用更多的方法至。

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

如前所述，这个double 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） time

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？在每个工作表中，用已回答的字符串替换它。

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屋！