How to make match() work with date in excel vba?

Question

I'm having problem making the match() work in excel VBA. The code is:

x = Application.Match("Sep 2008", Range("F1:F1"), 0)

The value in cell F1 is 9/1/2008.

Even if I changed Sep 2008 to 9/1/2008, it still doesn't return any value.

Any idea how to fix it?

Answer 1

The reason why Even if I changed Sep 2008 to 9/1/2008, it still doesn't return any value.

Is because when there is a Date in excel, Excel automatically converts that date to a numeric value, What you really want to search for is:

39692

This number is the number of days between 9/1/2008 and excel default of 1/1/1900

every date in excel is stored with a value like this. So the easiest way to handle this would be to convert what you see as a date to what excel sees as a date using CDate().

This by itself will give you an unuseful error that vba can't get the property.

That is because the Lookup_value can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value. Not a date so simply convert the now date value to a number to search for the matching number in the list using CLng()

Give this a shot it will also be much faster then using the Find alternative:

x = WorksheetFunction.Match(CLng(CDate("Sep 2008")), Range("F1:F1"), 0)

This should give you the result expected

To handle when no match is found try this Sub:

Sub MatchDate()
Dim myvalue As Double
Dim LastRow As Long

LastRow = Cells(Rows.Count, "F").End(xlUp)

On Error GoTo NotFound
myvalue = WorksheetFunction.Match(CLng(CDate("Sep 2008")), Range("F1:F" & LastRow), 0)
MsgBox (myvalue)
End

NotFound:
MsgBox ("No Match Was Found")
End

End:
End Sub

Answer 2

Your best bet is to use .Find(). This will return a range if found or nothing if not.

Set x = Range("F1:F1").Find(CDate("Sept 2008"), , , xlWhole)

If you wanted the column number:

x = Range("F1:F1").Find(CDate("Sept 2008"), , , xlWhole).Column

With capture of not found

Sub test()

    Dim y As Date, x As Variant, c As Long
    y = CDate("Sep 2008")
    Set x = Range("1:1").Find(y, , , xlWhole)
    If Not x Is Nothing Then
        c = x.Column '<~~found
    Else
        Exit Sub 'not found
    End If

End Sub

Answer 3

Bottom line:

• use WorksheetFunction.Match(CDbl(date), range, 0)

• Alternatively, use a Date cell's Value2 property (which will also be a Double) instead of Value for the search key.

CLng suggested in other answers would discard the time part of the date.

The same problem exists for the Currency data type but you can't use CDbl for it (see below for options).

---

Range.Value2 Property (Excel) article suggests that Date and Currency types are "special" in that they have an "internal representation" that's in stark contrast with displayed value. Indeed:

• Date is internally represented as IEEE 64-bit (8-byte) floating-point numbers where the integer part is the date and fractional part is the time
• Currency is also 8-byte but is treated as a fixed-point number with 4 fractional digits (an integer scaled by 10'000)

Apparently, Match compares these internal values for performance reasons. So, we must ensure that they, rather than the readable representations, match exactly.

Since Date is already floating-point internally, CDbl(date) doesn't actually change the data.

For the Currency type, CDbl does change data, so it's out of question. So either

• use the exact representation of the key (to 4 fractional digits) this way or another if you require exact match, or
• make the cells in the range actually be formulas with Round) if the value to compare with comes from elsewhere and/or you only require equality to 2 fractional digits

Answer 4

This way it works using this method:

Nbr,L, C as Integer

Datedeb as date

nbr = WorksheetFunction.Match(CLng(CDate(Datedeb)), Range(Cells(L, C), Cells(L + 100, C)), 0)

Answer 5

I think I can safely assume that the value in F1 is a date. In you code "Sep 2008" is a string. You will never be able to get a successful match as long as your datatypes are inconsistent. If you are looking for a date, then make sure that the first parameter is a date.

Dim dSearchSDate As Date

dSearchSDate = "01/Sept/2008"

x = Application.Match(dSearchSDate, Range("F1:F1"), 0)

Here is another possible approach.

Sub temp()
Dim x
Dim dSearchSDate As Date

    dSearchSDate = "01/Sept/2008"

    If ThisWorkbook.Worksheets(1).Range("F1:F1").Value = dSearchSDate Then
        Debug.Print "Found it!"
    Else
        Debug.Print "Doh!!"
    End If

End Sub

Answer 6

I know this post is old, but I had the same issue, and did find the answer.

To make it work, you first need to make VBA see the same data formatting as it appears in your excel spreadsheet :

YourVar = Format("YourDate","mmm-yyyy") YourResult = Application.match(Clng(Cdate(YourVar)), YourRange, 0)

Regards

Gilles