Forum Discussion
Finding a date on another sheet.
This is a known problem. Excel VBA is very finicky when it comes to finding a date in a range.
Two workarounds:
1) If the dates in WorkRange are constants, not the result of formulas, change xlValues to xlFormulas:
Set MyEdit = .Find(EntryDate, , xlFormulas, xlWhole, xlByColumns, , True)
- or -
2) Leave xlValues alone, and specify the first argument formatted exactly as on the worksheet:
Set MyEdit = .Find(Format(EntryDate, "dd-mm-yyyy"), , xlValues, xlWhole, xlByColumns, , True)
or
Set MyEdit = .Find(Format(EntryDate, "mm-dd-yyyy"), , xlValues, xlWhole, xlByColumns, , True)
depending on which format the cells use.
This is a known problem. Excel VBA is very finicky when it comes to finding a date in a range.
Two workarounds:
1) If the dates in WorkRange are constants, not the result of formulas, change xlValues to xlFormulas:
Set MyEdit = .Find(EntryDate, , xlFormulas, xlWhole, xlByColumns, , True)
- or -
2) Leave xlValues alone, and specify the first argument formatted exactly as on the worksheet:
Set MyEdit = .Find(Format(EntryDate, "dd-mm-yyyy"), , xlValues, xlWhole, xlByColumns, , True)
or
Set MyEdit = .Find(Format(EntryDate, "mm-dd-yyyy"), , xlValues, xlWhole, xlByColumns, , True)
depending on which format the cells use.
Hi Hans
Thank you very much. I have tried both your suggestions and both find the required date in the other sheet when it is present - so I will be able to proceed with the programming.
I have also learnt something new. From the vba help on ".Find" I had no idea that the "LookIn" field given as "The type of information" (which did not mean anything in particular to me) could include such terms as xlFormulas which could equate different date formats! Sometimes the standard help facility just isn't helpful, or helpful enough!
I congratulate you on how clear and concise your help was to my problem and also helpful in understanding issues with dates in general.
Sue
