Solved: WorksheetFunction.Match can't find date

[mikerickson]

Try
[vba]dt = CDbl(Range("A1"))
'or
Debug.Print WorksheetFunction.Match(CDbl(dt), Range("A:A"), 0)[/vba]

The data type of a worksheet cell's value is either Double, String, Boolean or Error.

I do not like the way that Excel VBA handles reading dates in cells.
The number formatting of the cell actually changes the data type read by VBA.
(note that the code below (with out the CDbl) does not error.)
The data type of the value of the cell does not change, but the data type of the variable dt is dependent on the number formatting.
[VBA]Sub FindDateTime()
Dim dt As Variant ' Ideally, this should be DateTime, but that's not allowed here.

Range("A1") = Now ' Write date/time in the sheet.
Range("A1").NumberFormat = "General"
dt = Range("A1") ' Retrieve same date/time.
Debug.Print dt ' Check if it looks OK.
Debug.Print WorksheetFunction.Match(dt, Range("A:A"), 0) ' Find same date/time in same column.
End Sub[/VBA]

Frustrating, but converting all read-from-sheet dates to type Double makes it smoother. This uses the type declaration to force the conversion.

[VBA]Sub FindDateTime()
Dim dt As Double ' force dt to be a sheet worthy data type.

Range("A1") = Now ' Write date/time in the sheet.

dt = Range("A1") ' Retrieve same date/time.
Debug.Print dt ' Check if it looks OK.
Debug.Print WorksheetFunction.Match(dt, Range("A:A"), 0) ' Find same date/time in same column.
End Sub[/VBA]

Automatic type conversion, like all tools of the Devil, look good at first glance, but in the long run they'll bite.