VBA - inconsistent date format

[yky]

I store dates in the format of dd-mm-yyyy. When searching a date, my VBA code always reports date not found. So, I put in two message boxes to check the date format (see the following code). Both boxes report date as, say, 10-7-2011. However, when I open the spreadsheet and select find, the date in the find windows is the format of mm/dd/yyyy, e.g. 7/10/2011. No wonder the date is not found.

What causes the inconsistency and how do I rectify the problem?

=======================================
MsgBox ("date is " & voucherDate)
............
............
Set cell = rng2.Find(voucherDate, lookat:=xlWhole)
If Not r Is Nothing Then
MsgBox (voucherDate & " found")
Else
MsgBox (voucherDate & " not found")
End If

 

[AlphaFrog]

I think the problem is that you are searching for a Text string that looks like a date but is only Text. You need to search for a Serial Date.

Dates And Times In Excel

If you .Find a Serial date and not a text-date, then the format of the date in the cells shouldn't matter.

Example:

    [COLOR="Red"]Dim voucherDate As Date[/COLOR], cell As Range

    [COLOR="Red"]voucherDate = DateValue("July 10, 2011")[/COLOR] [COLOR="Green"]' you could use almost any date format to define voucherDate
    'voucherDate = DateValue("7/10/2011")
    'voucherDate = DateValue("10-Jul-11")[/COLOR]

    MsgBox ("date is " & voucherDate)

[COLOR="Green"]    'This will find the cell with the voucher date irregardless of the cell's date format
    ' because voucherDate is a serial date[/COLOR]
    Set cell = rng2.Find(voucherDate, lookat:=xlWhole)
    If Not cell Is Nothing Then
        cell.Select
        MsgBox (voucherDate & " found")
    Else
        MsgBox (voucherDate & " not found")
    End If