You can use the following syntax to use the MATCH function to look up a date in VBA:

Sub MatchDate()
    
'attempt to find date in range
On Error GoTo NoMatch
    MyMatch = WorksheetFunction.Match(CLng(CDate("4/15/2023")), Range("A2:A10"), 0)
    MsgBox (MyMatch)
End'if no date found, create message box to tell user
NoMatch:
    MsgBox ("No Match Found")
    EndEnd:End Sub

This particular example looks up the date 4/15/2023 in the range A2:A10.

If the date is found, a message box appears that tells the user which row in the range contains the date.

If the date is not found, a message box pops up that says “No Match Found” so the user knows the date does not exist in the range.

The following example shows how to use this syntax in practice.

Note: The CDate function converts a text string to a date in VBA.

Example: Use MATCH Function with Dates in VBA

Suppose we have the following list of dates in the range A2:A10 in Excel:

We can create the following macro to look up the row number that contains the date 4/15/2023 in the range A2:A10:

Sub MatchDate()
    
'attempt to find date in range
On Error GoTo NoMatch
    MyMatch = WorksheetFunction.Match(CLng(CDate("4/15/2023")), Range("A2:A10"), 0)
    MsgBox (MyMatch)
End'if no date found, create message box to tell user
NoMatch:
    MsgBox ("No Match Found")
    EndEnd:End Sub

When we run this macro, we receive the following output:

The message box tells us that the date 4/15/2023 was found in the 7th row of the range A2:A10.

Note that if we instead looked up the date 4/25/2023 then we would receive the following output:

Note: You can find the complete documentation for the Match method in VBA .