Ever since I’ve been using Jan Karel’s Flexfind, I don’t use Excel’s built-in Find. However, about half of the time, I don’t need any fancy options. I just need to find some text in the workbook. So I wrote a utility for myself called DDoE Quick Find. It has the following non-features:
- It searches the entire workbook – you can’t limit it to a sheet or range
- It searches only in values, not in formulas
- It only looks for partial matches
- It only displays the first 100 results
- There’s no replace facility
- It’s really slow on very large workbooks
The add-in puts a Quick Find control on the Edit menu. You start typing in the What box, and when the list is manageable, you select items in the Results box. Selecting an item navigates to that cell.
The above is an example of one of those very large, slow workbooks. If the workbook has more than half a million used cells, the message at the bottom turns red (otherwise it’s black). It’s just a little visual clue that you may be in for a wait. I don’t have any workbooks that big except for the one used in this example. However, the bigger the workbook, the more likely I need to search for something in it. But it’s also more likely that I can narrow it down to a particular sheet and use Flexfind.
It takes about four seconds to search on the above monster workbook. That doesn’t seem too bad, except that it’s four seconds every time I type a letter. The code uses the Change event, not the AfterUpdate event, because I don’t want to have to navigate out of the textbox to get the results. I need to build in a small delay so that the user can get three or four letters typed before it starts searching. I’m not sure how that will work with the events. Will it just queue up the event calls and run them anyway? I don’t know.
And to be fair, the first few letters don’t take long at all. Since it stops searching after it finds 100 results, typing “B” gets 100 results on the first page and is very fast. By the time I get to the “e” or the “i”, it has to look in considerably more cells before it gets to 100 matches, and that is what takes so long.
Monster workbooks aside, it seems to do what I want at a reasonable speed. You can download ddoequickfind.xla.zip.
You might consider using the keyup event to initiate the search…
… and the Keydown to stop it. Does require a boolean test in the search loop, so it won’t speed it up I guess.
If the workbook has more than 500k cells, I should use the AfterUpdate event. If less, use the Change event. What do you think of that?
If Me.IsLargeSearch Then
FillResults
End If
End Sub
Private Sub tbxWhat_Change()
If Not Me.IsLargeSearch Then
FillResults
End If
End Sub
Yeah, that seems to work. Downloadable file updated.
In Excel2003, an error is generated if the text in the found cell is longer than 2047 characters. You can replace the problem line in Private Sub FillResults with Me.lbxFound.AddItem Left$(rFound.Value, 2047).
This is yet another example of where there are undocumented and different limits to the lengths of text strings that various Excel objects and methods can deal with.
Fixed. Thanks Peter.
I suspect you could speed up the whole thing not using the additem method
Instead I’d suggest to use a matrix that can be loaded into the listbox .list=sq
I rewrote your application so that all the necessary code would be part of the userform. No other modules involved.
On Error Resume Next
If tbxWhat.Text = “” Then Exit Sub
lblWarning.Caption = “”
lblMessage.Caption = “”
ReDim sq(100, 2)
For Each sh In ActiveWorkbook.Sheets
lblWarning.Caption = Val(lblWarning.Caption) + sh.UsedRange.Cells.Count
c1 = “”
c2 = “”
Do
If c1 = “” Then
c1 = sh.UsedRange.Find(tbxWhat.Text, , xlValues, xlPart).Address
Else
c1 = sh.UsedRange.Find(tbxWhat.Text, sh.Range(c1), xlValues, xlPart).Address
End If
If Err.Number > 0 Then Exit Do
If c1 = c2 Then Exit Do
If c2 = “” Then c2 = c1
With lblMessage
sq(Val(.Caption), 0) = sh.Range(c1)
sq(Val(.Caption), 1) = c1
sq(Val(.Caption), 2) = sh.Name
.Caption = Val(.Caption) + 1
End With
Loop Until Val(lblMessage.Caption) > UBound(sq)
Err.Clear
If Val(lblMessage.Caption) > UBound(sq) Then Exit For
Next
lbxFound.List = sq
With lblMessage
.ForeColor = IIf(Val(.Caption) > UBound(sq), vbRed, vbBlue)
.Caption = “Cells Found: “ & IIf(Val(.Caption) > UBound(sq), “>”, “”) & .Caption
End With
lblWarning.Caption = “Cells to search: “ & Format(lblWarning.Caption, “#,##0”)
End Sub
You need to exclude hidden sheets so:
For Each sh In ActiveWorkbook.Sheets
If sh.Visible = xlSheetVisible Then….
As Hans Schraven says – technically it’s quicker to use an array. I use a similar system to find parts from a parts database and the array system is over 100 times quicker (the list box can have up to 1,000 entries).
Ian