Excel VBA Application.Match between Long Time and Custom format

Question

I can't for the life of me get this to work.

I have a column r of time values formatted as "yyyy-mm-dd hh:mm:ss.000 AM/PM" (this format is one of the Custom formats one can choose for a cell).

I also have a cell c containing a time value formatted as "*hh:mm:ss" (the first Time format).

I now want to find on which row i the c.Value is in range r. I do this using

i = Application.Match(c.Value, r, 1)

On this row I get the 'Run-time error '13': Type mismatch' and my guess is that it is because the cells in r has a Custom format, or because there is no date in c.Value.

How can I get this to work? Is there some other way I can get the placement of c.Value in the range r?

Code:

Sub test()
Dim r As Range
Set r = Range("A1:A10")
Dim c As Range
Set c = Range("B1")

Dim i As Integer
i = Application.Match(c.Value, r, 1) 'ERROR!

End Sub

SOLVED:

I didn't know Excel treats dates and times as numbers, so I got it to work when saving c.Value as a type Double before using it in the function.

The match will raise an error when the value is not found by the match function. You can trap this error if you Dim i as Variant (since the Match may return a Long/Integer or an Error type, you need a Variant container). — David Zemens
Yes I know, but I know the time is in there. But I managed to solve it, see edit in OP :) — darksmurf
Cheers. You can add an "answer" to your own question and mark it as accepted :) — David Zemens
"You can accept your own answer in 2 days", what the hell is the point of that?! — darksmurf

darksmurf darksmurf · Accepted Answer · 2015-02-20T20:26:45

SOLVED:

I didn't know Excel treats dates and times as numbers, so I got it to work when saving c.Value as a type Double before using it in the function.

Excel VBA Application.Match between Long Time and Custom format

1 Answers