Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match Date/Time to Date list | Excel Worksheet Functions | |||
Compare/Find Exact Date match with varying time | Excel Worksheet Functions | |||
Worksheet function match - run time error | Excel Worksheet Functions | |||
Date and Time Function | Excel Discussion (Misc queries) | |||
MATCH FUNCTION AND SERIAL DATE CONFLICTS | Excel Worksheet Functions |