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”.
Now 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 parenthese 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.
