Finding Fault

Using the FIND method in your VBA macro is a good way to locate a specific piece of data in your workbook, and can be much more efficient than looping through an array of cells. For example, say you have a massive table of data, but each record is identified with a unique key of some kind. The fastest way to get a reference to a specific record is with FIND:

Set rngSearch = sht.Range("B1:B5000")
Set rngFind = Nothing
Set rngFind = rngSearch.Find(strKey)
If Not rngFind Is Nothing Then
   Result = rngFind.Row
End If

Unfortunately, this method can let you down if you're not careful, due to it's association with the Excel FIND menu command. The problem is that the function contains a bunch of optional parameters for which the default value is not predictable. As the VBA help file states:

The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you donít specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.

The above code might work perfectly for years, but then the user of the spreadsheet, on some totally unrelated project, uses the Find dialog to look for a number format or something. Now the Macro stops working - key values aren't being found. Depending on the data and what you're searching for, you might not even notice the problem, and simply think that the data isn't there... Maddening!

So, to avoid surprises, be sure to explicitly define ALL of the method's parameters EVERY TIME. The above code, rephrased, should look like this:

Set rngSearch = sht.Range("B1:B5000")
Set rngFind = Nothing
Set rngFind = rngSearch.Find(What:=strKey, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not rngFind Is Nothing Then
  Result = rngFind.Row
End If