There are functions in VBA that you can use but there’s no function that you can use equivalent to MATCH. But there’s a way that we can use to use these functions. In VBA, there’s WorksheetFunction property that can help you to access the worksheet functions to use in a macro.
Using Match in VBA
As I said we are going to use the worksheet function in the macro, but there’s one thing that you need to take care of that when you use the worksheet function properly, it won’t show you the arguments name in the function, so make sure to check all the arguments that we need to specify in the match. Below is the sample shot for your reference.
Following is the example where we have a list of random numbers, and you need to match the number “943”.
To writing a code for this, open the visual basic editor, insert a new module, and follow the below steps:
Range("B1") = WorksheetFunction.Match(943, Range("A:A"), 0)- Enter the worksheet property and type a dot to get the list of functions.
- Select the match function or you can also type it.
- Type a starting-parentheses to specify the arguments.
- In the arguments, you need to specify the value to look for, the range to look into, and the type of match.
- In the end, use a cell reference to get the result of the function.
Now when you run the code, it enters the number 15 in the cell B1. Why? because the values “943” is in the cell 15th in the column A. And you see in the function code, we have referred to the entire column instead of the range.
Important Note
When you use WorksheetFunction property in VBA code, it won’t show you the name of the arguments, instead it will show you the arg1, arg2 so on. So, you need to know the arguments which you are going to define in the function.
For this, the best way is to go to the worksheet and enter the MATCH function there in a cell and then use the keyboard shortcut Ctrl + Shift + A to get the name of the arguments. Then you can copy those names and paste it into the code to get a reference for specifying the argument.
Otherwise, you can just remember the name of the arguments and then define them in the code.