Problems with Find and Date Fields in Excel macro
 

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

Problems with Find and Date Fields in Excel macro
 

See 'Dates In The Find Method' on Chip's WebPage.

http://www.cpearson.com/excel/DateTimeVBA.htm

It seems to me that your code is trying to find a string.

HTH
Paul
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------
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