CELLS.FIND doesn't find date in list

[ERIM]

XL2002 SP3 on XP Pro SP2

In my spreadsheet (one of many) I have a table consisting of dates in column A (in the format dd/mm/yyyy) which represent the first of each month, plus other data in columns B:D.

When the section of my VBA macro reaches the CELLS.FIND line, the active cell is always in the row immediately above the start of my table. The VBA code was originally generated using the macro recorder, so I know that when I used the EDIT/FIND function, the active cell became the cell containing the month I had entered.

In the VBA code I determine the date using the following statement...

rfld = "01/" & Mid("0" & Month(Now()) + 1, Len(Month(Now())), 2) & "/" & Year(Now())

...and use the variable in the cells.find code that follwos ...

Cells.Find(What:=rfld, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

 

[Jonmo1]

I think the problem is that VBA is making this line

rfld = "01/" & Mid("0" & Month(Now()) + 1, Len(Month(Now())), 2) & "/" & Year(Now())

A date in the format of mm/dd/yyyy (that's the default format)
But you're expecting dd/mm/yyyy

Try this instead

rfld = DateSerial(Year(Now()), Mid("0" & Month(Now()) + 1, Len(Month(Now())), 2), 1)

Hope this helps...

 

[ERIM]

For reasons best known to itself, the message board saved the message part way through me creating the post, and then wouldn't save the updated version when I edited it to complete!

Any way, I've now solved my problem with the addition of the line:
rfld=datevalue(rfld)
which now makes the format of the search string match the format of the dates in the table.

 

[ERIM]

Wow 'jonmo1' that was an impressive response time to my partly finished post as I was typing in my reply, but as you can see from the above, I've arrived at the same answer as you on my own!

Many thanks for the confirmation and reason which I should have realised earlier (I've had a lot of problems with the default American date format over the years!).