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.