Shakil Ahmed
Circumstances may arise when you need to use certain VBA codes that execute the operation from different worksheets, like you may need to use VLOOKUP in VBA code to find lookup value from another sheet. Today we are going to show you how to use the Excel VBA VLOOKUP in another worksheet.
Read more: VLOOKUP from Another Sheet in Excel
Before diving into the session, let’s get to know about our example dataset.
Here we have a dataset that contains a few well-known fiction writers with their respective date of birth and noteworthy work genre. This set of data is stored in the sheet called “Data”.
On another sheet called “Details”, we have stored the birthplaces for these authors.
Now we will execute the VLOOKUP operation through VBA code within the sheets.
Note that this is a basic dataset to keep things simple. In a real-life scenario, you may encounter a much larger and complex dataset.
Practice Workbook
You are welcome to download the practice workbook from the link below.
Excel VBA VLOOKUP to Find Values from Another Worksheet
1. Executing VLOOKUP Statically
We can write the VBA VLOOKUP code in such a way that for every execution we need to change that manually.
To show you examples, here we have introduced a Birth Place column to the author lists dataset. We aim to find the birthplace for each writer that is listed in the “Details” sheet.
Hope you have noticed that this sheet name is “Static Changes”, we have copied the data from the “Data” sheet here to keep that sheet intact and understand the changes easily.
Now open Microsoft Visual Basic for Applications window pressing the ALT + F11 (Or you can explore the Developer tab and click Visual Basic). And insert Module.
Here we have right-clicked at the project name which pops up the options, from there within Insert we found Module and clicked it.
Now write the following code in the module.
Sub VLookup()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Static Changes")
With ws
.Range("E4").Formula = "=VLOOKUP(B4,Details!$B:$C,2,0)"
End With
End SubHere we have written a SUB procedure, declared a variable ws that is a worksheet. And set the “Static Changes” in it.
Within this sheet, using Range(“E4”), we have set the cell where we want to insert the result. Hope you have understood that the result will be derived from the VLOOKUP formula.
Inside the VLOOKUP we have provided the author name as lookup_value where the “Details” sheet data was the lookup_array.
Now Run the code (for your convenience we have highlighted the Run button in the earlier image).
We have found the birthplace of the author Arthur Conan Doyle. Now if you want to find the result for the second author from our list you need to modify the code a bit: change the respective cell references.
Here we have changed the result containing cell reference from E4 to E5 and within VLOOKUP the lookup_value cell from B4 to B5. And Run it.
You will find the result. Now change the respective cell references for the rest of the values to find the results.
2. VBA VLOOKUP for a Selected cell in Another Worksheet
In the earlier section, we have shown how to use VBA VLOOKUP for static changes. That may seem irritating since we need to change the cell references every time before executing the code.
To get rid of the problem we will see how to use VLOOKUP for a selected cell. For this example, we have copied the data to the “Change Selection” sheet.
In our example, we will select a cell from the Birth Place column and our code will derive the birthplace for the author from that row.
Probably you have thought about how a mere selection will execute the code, absolutely correct. Only selecting a cell will not trigger the code. To execute the code, we need a button or shortcut key. Let’s proceed with the shortcut key.
From the Developer tab select the Record Macro option.
This will bring the Record Macro dialog box in front of you. Set the macro name and shortcut key to the respective fields.
Here we have set Vlookup_shortcut as the macro name and CTRL+SHIFT+J as the shortcut.
Now in Microsoft Visual Basic for Applications, open the module for the Vlookup_shortcut macro and write the following code
Sub Vlookup_shortcut()
'
' Keyboard Shortcut: Ctrl+Shift+J
'
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],Details!R4C2:R10C3,2,0)"
End Sub
Once we have set the macro name and shortcut key then the outline of the SUB procedure has been created. We have only provided a single line of code within it.
The ActiveCell denotes which cell is going to be the selected one. Here R and C are for row and column.
Our lookup_value is at the 3 columns left to the selected cell that’s why we have written RC[-3].
In general, the lookup_array cell reference is B4:C10, which means row 4 column 2 to row 10 column 3, so we have set R4C2:R10C3 (from the “Details” sheet).
Save the code, then select the cell and press the shortcut key (CTRL+SHIFT+J).
We have found the result we were looking for. Now select the next cell and press the shortcut key.
Similar to this approach, fill the Birth Place column.
3. Excel VBA VLOOKUP for Entire Data
In this section, we will see the VBA code to find the value for the entire data at once.
Read more: How to Find Value in Column Using VBA in Excel
Again, to keep the changes understandable, we have copied the dataset to the “Entire At Once” sheet.
Now open a module from Microsoft Visual Basic for Applications window, and write the following code.
Sub VlookupFull()
Dim authorWs As Worksheet, detailsWs As Worksheet
Dim authorsLastRow As Long, detailsLastRow As Long, x As Long
Dim dataRng As Range
Set authorWs = ThisWorkbook.Worksheets("Entire At once")
Set detailsWs = ThisWorkbook.Worksheets("Details")
authorsLastRow = authorWs.Range("B" & Rows.Count).End(xlUp).Row
detailsLastRow = detailsWs.Range("B" & Rows.Count).End(xlUp).Row
Set dataRng = detailsWs.Range("B4:C" & detailsLastRow)
For x = 2 To authorsLastRow
On Error Resume Next
authorWs.Range("E" & x).Value = Application.WorksheetFunction.VLookup( _
authorWs.Range("B" & x).Value, dataRng, 2, False)
Next x
End Sub
Here we have declared the worksheet variables to store the sheets. The authorsLastRow and detailsLastRow are for making the list dynamic.
While defining these two variables we have set the range start from cell B and keep counting to the end of it (until a value is inserted)
The dataRng variable to set the lookup_array range.
Then a FOR loop to go across the cells and derive the values.
Save and Run the code.
We have found the birthplaces for all the authors at once.
Conclusion
That’s all for today. We have listed several approaches to use the Excel VBA VLOOKUP in another worksheet. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know which approach you prefer. Don’t hesitate to notify any methods which we might have missed here.
