It's not really the format of the value that's important. It's the value inside
the cell.
And with date/times, I've had trouble using application.match in VBA. I would
expect that it's some sort of rounding difference.
Here's a routine that tries to use application.match and fails (with my test
data), but using application.evaluate will find the match.
Option Explicit
Sub testme()
Dim res As Variant
Dim LookupRng As Range
Dim wks As Worksheet
Dim myDate As Date
Dim myFormula As String
Set wks = Worksheets("sheet1")
myDate = DateSerial(2008, 1, 19) + TimeSerial(12, 48, 0)
With wks
Set LookupRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With
'this returned an error
res = Application.Match(CDbl(myDate), LookupRng, 0)
If IsError(res) Then
MsgBox "no match"
Else
MsgBox "Match on row: " & res
End If
'but this worked ok
myFormula = "MATCH(TEXT(" & CDbl(myDate) _
& ",""yyyymmdd hhmm""),TEXT(" _
& LookupRng.Address(external:=True) _
& ",""yyyymmdd hhmm""),0)"
res = Application.Evaluate(myFormula)
If IsError(res) Then
MsgBox "no match"
Else
MsgBox "Match on row: " & res
End If
End Sub
The myFormula string is the same kind of thing as this array formula in a
worksheet cell:
=MATCH(TEXT(C1,"yyyymmdd hhmm"),TEXT(Sheet1!$A$1:$A$22,"yyyymmdd hhmm"),0)
If you need to use seconds, then both formatting strings will look like:
"yyyymmdd hhmmss"
