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")
End
End:
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")
End
End:
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:
The message box tells us that the date 4/25/2023 was not found in the range.
Note: You can find the complete documentation for the Match method in VBA here.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Use XLOOKUP
VBA: How to Use INDEX MATCH
VBA: How to Use INDEX MATCH with Multiple Criteria
