Table of Contents

Vlookup

Use the WorksheetFunction property in Excel VBA to access the VLOOKUP function. All you need is a single code line.

Situation:

VBA Vlookup Example

1. Place a command button on your worksheet and add the following code line:

Range(“H3”).Value = WorksheetFunction.VLookup(Range(“H2”) Range(“B3:E9”) 4 False)

When you click the command button on the worksheet Excel VBA uses the VLOOKUP function to lookup the salary of ID 53 and places the result into cell H3.

VBA Vlookup Result

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.

No Match

Result when you click the command button on the sheet:

Unable to get the VLookup property of the WorksheetFunction class

3. To replace this error with a friendly message update the code as follows.

On Error GoTo InvalidValue:

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:

Not Found

Note: visit our page about error handling to learn more about this topic.

Leave A Comment

Excel meets AI – Boost your productivity like never before!

At Formulas HQ, we’ve harnessed the brilliance of AI to turbocharge your Spreadsheet mastery. Say goodbye to the days of grappling with complex formulas, VBA code, and scripts. We’re here to make your work smarter, not harder.

Related Articles

The Latest on Formulas HQ Blog