Vlookup in Excel VBA
Use the WorksheetFunction property in Excel VBA to access the VLOOKUP function. All you need is a single code line.
Situation:
1. Place a command button on your worksheet and add the following code line:
When you click the command button on the worksheet, Excel VBA uses the VLOOKUP function to look up the salary of ID 53 and places the result into cell H3.
Note: visit our page about the VLOOKUP function to learn more about this Excel function.
If the VLOOKUP function cannot find a match, Excel VBA returns the "Unable to get the VLookup property of the WorksheetFunction class" error.
2. For example, enter the value 28 into cell H2.
Result when you click the command button on the sheet:
3. To replace this error with a friendly message, update the code as follows.
Range("H3").Value = WorksheetFunction.VLookup(Range("H2"), Range("B3:E9"), 4, False)
Exit Sub
InvalidValue: Range("H3").Value = "Not Found"
Result when you click the command button on the sheet:
Note: visit our page about error handling to learn more about this topic.
If you're new here, welcome to Excel Easy! Join over 1 million monthly Excel learners. You can find popular courses here: Data Analysis in Excel and Excel VBA.
