Pretty sure I can explain this, I just got burned by this yesterday.

From the Excel VBA help file:

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.
Ok, this is a little unclear at first. What its saying is that those four settings are saved statically IN THE GUI (Excel application), NOT IN THE PROJECT FILE!
Further more, these settings are saved ANY time Find is used, including when Find is used from the GUI! (from the Edit menu, or Ctrl-F)

This means that if you don't explicitly call these settings each time, you are at the mercy of whatever the last usage was, wether it was the GUI or VBA code. If you are using the Ctrl-F to try and debug your .Find problems, you could be making a recursive mess. Try it out:

[vba]
Sub Test1()
expression.Find(What, , xlValues, xlWhole, xlByRows)
End Sub

Sub Test1()
expression.Find(What, , xlValues, xlPart, xlByRows)
End Sub
[/vba]

Run the subs, and then go into the GUI Find box and check to see that the options have changed. Frankly, at first I didn't believe my own eyes, since this seems to be the only VBA Method that acts like this.


Here is my solution:

The Help says to specify ALL options EVERY time, but this is over kill: if the first .Find in your code specifies the options, and all of the following uses of .Find use the same options, then you don't have to specify every time. Also, specifiying every time can make your code span multiple lines, and therefore be a little harder to read.

So, if ALL of the .Find uses in a proceedure use the same options, I only specifiy it once, during the first usage. If I have multiple .Finds that need different options, then I specify them every time, becuase its too hard to keep track of.

NOTE: Most of the lookup Ranges I use are 1-dimensional (all cells in one row OR one Column). This means that I don't need specifiy SearchOrder, becuase it doesn't make a bit of difference for 1-D Range.


EDIT: Oh yeah, you are having problems with dates. Remember how Excel stores dates: depending on what your data looks like, you might be able to tweak the finding behavior by switching the LookIn option between xlFormulas and xlValues.