Table of Contents
VBA’s MATCH function can be used with dates to search for a specified item in an array and return its relative position. It works by returning the relative position of the item that is closest in value to the item we are searching for. To use the MATCH function with dates, you need to use the same date format for both the item we are searching for and the items in the array. Additionally, you can also use the Match_Type parameter to indicate the type of match you want, such as exact or approximate.
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:
Note: You can find the complete documentation for the Match method in VBA .