Pexels With the above data, I would like to return the employee in cell F4, which relates to the employee number that appears in cell E4. This task proves to be simple when using the Vlookup as a worksheet function. When running the function in VBA, there are a few additional steps to completing the task. To start, go to the view tab and click the view macros button.
View Macros Button
Next, create a name for the macro and click the plus button. On a Windows system, the create button will need to be clicked.
Name and Create a Macro
A new VBA module will appear like the one below. Each macro must start with Sub followed by the name of the macro with the ending End Sub.
VBA Module
In the example code that I created below, the first section identified is the range where I want the result to appear. In this case, I set this value as employee but any variable will do. Notice the syntax used to identify a cell. The cell needs an added identifier to let the back end of Excel know what a cell or range of cells is. Set employee = Range(“F4”)
VBA Code Example 1
On the next line of code, we want to establish that employee (or cell F4) is a value and will be equal to whatever the function returns. Without the “.Value” after the employee variable, the macro will run with no result. Employee.Value = Next, for the function section, “Application.WorksheetFunction.” needs to be chosen before using available Excel functions in VBA. Following, the function arguments are the same as they are when using Vlookup in a spreadsheet formula with the exception of formatting cell locations in VBA. Application.WorksheetFunction.VlookupRange(“E4”), Range(“B3:C7), 2, True) See the below arguments of the spreadsheet Vlookup function in the illustration.
Vlookup Formula Arguments
Running this function from the view macros option will allow the macro to execute and populate cell F4 of the example with the result.
Results
Below is another simple Vlookup VBA example with a few differences. First, instead of using a specific range of cells, I used column ranges to specify the lookup array. More importantly, I set variables to represent the cells for the lookup value and array and used those variables directly in the function arguments.
VBA Code Example 2
This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional. © 2022 Joshua Crowder
