Find all matches in workbook using Excel VBA

Question

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.

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 

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?

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

Answer 1

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 

Answer 2

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:

'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 

Usage is the same as native .Find, but here is a usage example as requested:

Sub test()   Dim SearchRange As Range, SearchResults As Range, rng As Range     Set SearchRange = MyWorksheet.UsedRange     Set SearchResults = FindAll(SearchRange, "Search this")          If SearchResults Is Nothing Then         'No match found     Else         For Each rng In SearchResults             'Loop for each match         Next     End If End Sub