Solved: WorksheetFunction.Match can't find date

[Sebastian H]

I'm trying to find a row by a given date. This works all right with the worksheet function MATCH, but when I try to do the same in VBA, I get an error. I boiled the problem down to the following:

[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.
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] This gives me the error

Run-time error 1004
Unable to get the match property of the WorksheetFunction class.

It doesn't seem to be a rounding error; I tried that with a series of date/times and used match_type 1, but to no avail.
I'm using Office XP on Windows XP.

[frank_m]

maybe
[vba]Debug.Print WorksheetFunction.Match(CLng(dt), Range("A:A"), 0)[/vba] or
[vba]Debug.Print WorksheetFunction.Match(CDate(dt), Range("A:A"), 0)[/vba]

[Sebastian H]

No, that doesn't change anything. (I would have been surprised if it did, although I'm getting used to getting surprised by VBA. ）

BTW, you can easily try this out yourself, that's why I boiled down the problem to only a few lines of code.

[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.

[frank_m]

is returning the row number of the first match found for me (unless there is no matching date, then it errors.)
[vba]Debug.Print WorksheetFunction.Match(CLng(dt), Range("A:A"), 0) [/vba]

[Sebastian H]

Thank you, that works for me, and thanks for offering several solutions.

I really like your solution of declaring it as Double. There's no reason to declare it as Variant; I only did that because I thought VBA would then automatically choose what works, but you're right, it's a tool of the Prince of Darkness.

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

BTW, neither Frank's 'CLng()' nor your '.NumberFormat = "General"' worked for me. Maybe the latter has to do with the language settings; I probably have a different date format. But that doesn't matter now that we have a great solution。