match function - date and time
 

Good evening Group,

Have been looking in the archive but not found a complete answer.

Is it possible to use the MATCH function in VBA when looking
for a date/time in format "YYYY-MM-DD hh:mm"? The lookup array
is in the same format.

Brgds

CG Rosen

match function - date and time
 

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"



"CG Rosén" wrote:

Good evening Group,

Have been looking in the archive but not found a complete answer.

Is it possible to use the MATCH function in VBA when looking
for a date/time in format "YYYY-MM-DD hh:mm"? The lookup array
is in the same format.

Brgds

CG Rosen

--

Dave Peterson