Cells.Find xlDate does not find first date in sheet unless date format is dd/mm/yyyy

Hello,

I have an excel spread sheet that contains the following data:
B1 = "01/10/2013"
C1 = "02/10/2013"
D1 = "03/10/2013"

Cells are formatted with a custom format: dd-mmm-yy

Please Login or Register  to view this content.

loc.Address is returned as $C$1 but if I change the cell formatting to short date then loc.Address correctly returns $B$1.

Is there anyway to find the first cell with a date in without changing the cell formatting? I am using .Find as the spreadsheet date location may change and eventually I want to open a worksheet (from another workbook), iterate through the date cells pulling the data in and the close it again. I don't control formatting of the worksheet to be read in and therefore would prefer not to have to open the worksheet and change the date format first.

Cheers,

Tom

TMS,

Thanks for the quick response. xlFormulas does work, but alas the dates, for some reason, are actually formulas with D1 (in this example) an actual date and C1 "=D1-1" and B1 "=C1-1".

Any other suggestions? I can do workarounds if required but just wanted to check I wasn't missing something.

Cheers,

Tom

Brilliant - Application.FindFormat.NumberFormat = "d-mmm-yy" works exactly right. Thanks a lot.

I should have realised that xlDate was being resolved into it's numerical constant of 2 and therefore that is what it was searching for. Reading the Excel Developer reference it said:

What: Required, Variant - The data to search for. Can be a string or any Microsoft Excel data type.

Obviously it means it can search for specific numbers, dates, etc. as well as strings but given the wording I thought xlDate might work just to search for any date. When it started to return results, albeit confusing ones, I thought it might actually work that way.

Anyway - all sorted - thanks again, and TMS thanks for not giving up.

Cheers,

Tom