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

excel, vba

Solution

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

Problem

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?

Original source