Vlookup is an essential function within Excel and has become an important part of data processing. It provides some functionality that you can usually associate with a full blown database.
But what if you want to automate this function? Yes, it is possible, especially if you know how to use Excel’s native automation language, VBA. Here’s how you can automate the VLOOKUP function in Excel VBA.
Syntax of Vlookup
The syntax of the Vlookup function is fairly simple, but there are four aspects you should pay attention to, even when automating it in Excel VBA.
The Destination table has matching columns, with no subcategory or product name data. Note that the values of its Order ID column are also present in the same column of the lookup table.
You can drag the formula from cell B2 down to the end of the column, B17. Repeat this process for column C as well. Each subsequent entry will automatically update the values in both columns.
Vlookup Function of Excel VBA
There are many similarities to the VLOOKUP function whether you’re using it directly in Excel or through VBA. Whether you are a data analyst working with rows of data, or an inventory manager dealing with new products on a regular basis, you can benefit from using VLookup.
When working with dynamic look-up ranges, it is always best to automate your formulas in Excel to avoid having to apply formulas over and over again. By automating your VLOOKUP function in VBA, you can perform multi-column calculations with one click.
1. Make the necessary setup
Start by opening the coding editor in a new Excel workbook (press Alt+F11 or navigate to the Developer tab) and add a module to start writing your code. Within the code editor, add the following lines to the top of the coding window to create a subroutine.
A subroutine is a container for your VBA code and is important for it to run successfully. One option is to create a VBA User Form to make your UI more interactive.
2. Declare Your Variables and Create Your Reference Range
First of all, you need to declare the variable data type using Dim statement.
Next, create a final variable to store the value of the last populated row within your lookup range. The END LINE function uses the END (xldown) function to count the last line reference within the specified range.
In the above example, the destination table spans A2:C17, while the source table spans F2:H17. The Vlookup formula will loop through each value stored within column A, look it up within the source table, and paste matching entries into columns B and C.
3. Write a loop to cycle through each lookup value
Before writing the loop, define the initial row values, 2. Defining the initial values for your for loop is essential because you are dealing with dynamic elements. In the destination table, row 2 is the first row of data. Change this value if your data starts on a different row.
You can use a do-while loop to process each row starting at row 2 and ending at row 17. Like the rest of the code, this aspect is dynamic; The loop will run until the condition is false. Use a condition to check for a non-empty value in the first cell of the current row.
These statements set the values of cells in the current row in columns 2 and 3, respectively. They use the Worksheet Function object to call the VLookup function, passing the value corresponding to column 1 to search. They also pass the range you defined earlier and the index of the relevant column to get the final value.
Each time the loop runs, it increments the value of i by 1. Remember, the starting line has a value of 2, and increments each time the loop runs. Use the loop keyword to set the end of the code block of the loop.
When you run the entire code, it will populate the results in both columns, based on the values in the source table.
4. Create a button to run the code
With Code, you can create a button in your Excel workbook to run it with a single click. Place your desired shape on a blank Excel sheet, right-click on it, and click on the Assign Macro option.
When you want to run your code, click the button to see how the data populates immediately.
Using Excel VBA to Automate Lookup Functions
Excel VBA is a flexible language, well geared towards easing automation within various aspects of MS Excel. Many options exist in MS Excel VBA, from automating Excel functions to automatically creating detailed Pivot Tables.