Problems with Find and Date Fields in Excel macro

  • Thread starter Thread starter Nick Marshall
  • Start date Start date
N

Nick Marshall

Using Edit | Find interactively to look for a date in a
cell works fine, just like looking for a text string. If
I record it as a macro, however, I get the expected macro:

Cells.Find(What:="31/12/2002", After:=ActiveCell,_
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False).Activate

but this fails with runtime error 91 when I try to re-run
it (ie, it failed to find anything).

Finding text works fine in a macro and amending the
above "date" macro to use:

What:=DateSerial(2003, 12, 31), rather than "31/12/2002"
also works so it seems like some sort of formatting bug
related to the fact that it's a date.

HOWEVER, I only got the idea to try this from the MS
Press book "Excel 2002 VBA Step By Step" and that book
seems to think it works. Any thoughts anyone? Could it
be something to do with a mismatch between US and UK date
formats?

Nick
 
Back
Top