Using MATCH with Dates

[polarisking]

The following seven date values are contained in Column A rows 1 through 7
7/25/2016
7/26/2016
7/27/2016
7/28/2016
7/29/2016
7/30/2016
7/31/2016

I'm getting stuck using MATCH within VBA using Date lookups. Keeps coming back with a 1004 - not found message. Change the values to animal names and the match argument to one of the names, and it works fine. I must not be handling the date properly, but I cannot figure out what the problem is.

Sub Chandoo()

    Debug.Print WorksheetFunction.Match(CDate("7/28/2016"), Range("A1:A7"), 0)

End Sub

 

[Chihiro]

Since Excel stores date in value (ex: 42579 for 7/28/2016) you need convert CDate to Long or Double. So something like below should work.

Sub test()
Debug.Print WorksheetFunction.Match(CLng(CDate("7/28/2016")), Range("A1:A7"),0)
End Sub

 

[polarisking]

Chihiro, you beat me me to it! Thank you for your response.

The KB reference is https://support.microsoft.com/en-us/kb/213643