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 VBA VLOOKUP in 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.
Download Workbook
You are welcome to download the Excel file and practice while reading this article.
5 Methods to Use VBA VLOOKUP to Find Values in Another Worksheet in Excel
Here, we have a dataset that contains the Author Name, DOB, and Famous for columns. This set of data is stored in the sheet called “Data”.
On another sheet called “Details”, we have stored the Birth Place for the authors.
Furthermore, we will execute the VLOOKUP operation through the VBA code within the sheets. Here, we used Excel 365. You can use any available Excel version.
Here, note that this is a basic dataset to keep things simple. In a real-life scenario, you may encounter a much larger and more complex dataset.
1. Using VLOOKUP for Static Changes in Another Worksheet
Here, we will show you how you can use VBA VLOOKUP in another worksheet 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 of each writer that is listed in the “Details” sheet.
Hope you have noticed that this sheet’s name is “Static Changes”, we have copied the data from the “Data” sheet here to keep that sheet intact and understand the changes easily.
Steps:
Here, you can go to the Developer tab >> select Visual Basic to open the VBA Editor window.
At this point, a VBA Editor window will appear.
- Then, from the Insert tab >> select Module.
After that, we will write the following code in the Module.
Sub VLookup_Static_Change()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Static Changes")
With ws
.Range("E5").Formula = "=VLOOKUP(B5,Details!$B:$C,2,0)"
End With
End Sub
Code Breakdown
- Here, we declare Vlookup_Static_Change as the Sub.
- Within this sheet named Static Changes, in Range(“E5”), we want to place the result. Hope you have understood that the result will be derived from the VLOOKUP formula where we mentioned the required values..
- Inside the VLOOKUP we have provided the author name as lookup_value where the “Details” sheet data was the lookup_array and the column_index-number is 2 which will return the values from the Birth Place
- Afterward, we will Run the code (for your convenience we have highlighted the Run button in the earlier image).
As a result, we have found the birthplace of the author Arthur Conan Doyle in cell E5.
- Afterward, 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.
As a result, you can see the complete Birth Place column.
Read More: Excel VLOOKUP to Return Multiple Values Vertically
Similar Readings
- VLOOKUP with Drop Down List in Excel
- 10 Best Practices with VLOOKUP in Excel
- VLOOKUP with Two Lookup Values in Excel (2 Approaches)
- VLOOKUP Formula to Compare Two Columns in Different Sheets!
2. Applying VLOOKUP to Change Selection of Another Worksheet in Excel
In the earlier method, we have shown how to use Excel VBA VLOOKUP in another worksheet 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 of 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.
Steps:
- In the beginning, From the Developer tab select the Record Macro option.
This will bring the Record Macro dialog box in front of you.
- After that, we set the Macro name as Vlookup_shortcut.
- Then, we set CTRL+SHIFT+J in the Shortcut key box.
Here, you can set the Macro name and Shortcut key according to your choices.
Afterward, we followed the Steps described in Method-1 to bring out the VBA Editor window.
- 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!R5C2:R11C3,2,0)"
End Sub
Code Breakdown
- 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 B5:C15, which means row 5 column 2 to row 11 column 3, so we have set R5C2:R11C3 (from the “Details” sheet).
- Then, save the code >> go back to your Worksheet.
- After that, select cell E5 >> press the shortcut key CTRL+SHIFT+J.
Therefore, we have found the result we were looking for.
Along with that, you can see the formula in the Formula Bar.
- Afterward, we will drag down the formula with the Fill Handle tool.
Hence, you can see the complete Birth Place column.
Read More: VLOOKUP Example Between Two Sheets in Excel
3. Use VLOOKUP to Find All Values from Another Worksheet
In this method, we will see the VBA code to find the value for the entire data at once. This method is a quick and handy method for Excel VBA VLOOKUP in another worksheet.
Again, to keep the changes understandable, we have copied the dataset to the “Entire At Once” sheet.
Steps:
- First of all, we followed the Steps described in Method-1 to bring out the VBA Editor window.
Then, we write the following code.
Sub Vlookup_Entire_Sheet()
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("B5: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
Code Breakdown
- Here we have declared the worksheet variables to store the sheets. Then used authorsLastRow and detailsLastRow to get the number of the last row of both sheets to make the list dynamic.
- While defining these two variables we have set the range to 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
- Then a FOR loop goes across the cells and derives the values.
- After that, we will Save the code >> press the Run the code.
Therefore, we have found the Birth Place of all the authors at once.
Read More: Excel Dynamic VLOOKUP (with 3 Formulas)
4. Using VLOOKUP to Get Values for a Particular Sheet
In this method, we will use the VBA VLOOKUP for a particular worksheet. This method is helpful when we want the outcome of the VBA in a definite worksheet.
Here, to keep the changes understandable, we have copied the dataset to the “Definite Sheet”.
Steps:
- First of all, we followed the Steps described in Method-1 to bring out the VBA Editor window.
- Then, we write the following code.
Sub Lookup_Birth_Place()
Dim ws_worksheet As Worksheet
Dim rng_range As Range
Dim start_Name As Stringstart_Name = Range("B5")
Set ws_worksheet = Sheets("Details")
Set rng_range = ws_worksheet.Range("B5:C11")
Sheets("Definite Sheet").Range("E5"). _
Formula = Application.WorksheetFunction.VLookup(start_Name, rng_range, 2, False)
End Sub
Code Breakdown
- We declare Lookup_Birth_Place as the Sub
- From the Details sheet, it will look up the values for the Definite Sheet.
- Next used Range(“E5”), to set the cell to insert the result. Hope you have understood that the result will be derived from the VLOOKUP
- Inside the VLOOKUP we have provided the author name as lookup_value where the “Details” sheet data was the lookup_array and the column_index-number is 2 which will return the values from the Birth Place
- After that, Save the code >> Run the code.
Note that this code will only execute the result in a particular sheet. Even if we run the code in a different sheet, the result will be shown in the Definite Sheet.
As a result, we have found the birthplace of the author Arthur Conan Doyle in cell E5.
- 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.
Hence, you can see the complete Birth Place column.
5. Using VBA If…Else Statement to VLOOKUP Value in Another Worksheet
In this method, we will use the If..Then..Else statement for Excel VBA VLOOKUP in another worksheet.
Here, to keep the changes understandable, we have copied the dataset to the “Definite Sheet”.
Steps:
- First of all, we followed the Steps described in Method-1 to bring out the VBA editor window.
- Then, we write the following code.
Sub search_birth_place()
Dim ws_1 As Worksheet, ws_2 As Worksheet
Dim search_birthplace As Variant
Set ws_1 = Worksheets("Details")
Set ws_2 = Worksheets("IF ELSE Statement")
On Error Resume Next
search_birthplace = Application.WorksheetFunction.VLookup(ws_2.Range("B5"), _
ws_1.Range("B5:C11"), 2, False)
On Error GoTo 0
If (IsEmpty(search_birthplace)) Then
ws_2.Range("E5").Formula = CVErr(xlErrNA)
Else
ws_2.Range("E5").Value = search_birthplace
End If
End Sub
Code Breakdown
- We declare search_birth_place as the Sub
- Here we have declared the worksheet variables to store the sheets.
- We used the If Else statement to execute the code.
- Inside the VLOOKUP we have provided the author name as lookup_value where the “Details” sheet data was the lookup_arrayand the column_index-number is 2 which will return the values from the Birth Place
- After that, Save the code >> Run the code.
As a result, we have found the birthplace of the author Arthur Conan Doyle in cell E5.
- 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.
Hence, you can see the complete Birth Place column.
How to Use VBA VLOOKUP to Get Values from Another Workbook in Excel
Here, we will show how you can use Excel VBA VLOOKUP in another workbook.
In the following dataset, you can see the Information of Author is present in a workbook named Another Workbook.
On the other hand, we have a workbook named Excel_VBA_VLOOKUP_in _Another_Worksheet. Here, you can see the incomplete Birth Place column.
Next, we will extract the Birth Place of the corresponding Author Name, and we will insert this in the following dataset.
Steps:
- First of all, to open the VBA Editor window, we will press the ALT+F11 keys.
Here, you can go to the Developer tab >> select Visual Basic to open the VBA Editor window.
At this point, a VBA Editor window will appear.
- Then, from the Insert tab >> select Module.
- After that, we will write the following code in the Module.
Sub Lookup_in_another_workbook()
Dim work_book As Workbook
Dim work_sheet As Worksheet
Dim rng_range As Range
Dim start_name As String
Set work_book = Workbooks("Another Workbook.xlsx")
Set work_sheet = work_book.Sheets("Details")
Set rng_range = work_sheet.Range("B5:C11")
start_name = Range("B5")
ActiveCell = Application.WorksheetFunction. _
VLookup(start_name, rng_range, 2, False)
End Sub
Code Breakdown
- Here, we declare Lookup_in_another_workbook as the Sub.
- Here, we have declared the worksheet and workbook variables to store the sheet and workbook name.
- Within the workbook “Another Workbook”, in the sheet “Details”, using Range(“B5”), 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
- Inside the VLOOKUP we have provided the author name as lookup_value where the “Details” sheet data was the lookup_arrayand the column_index-number is 2 which will return the values from the Birth Place
- Afterward, we will Run the code (for your convenience we have highlighted the Run button in the earlier image).
As a result, we have found the birthplace of the author Arthur Conan Doyle in cell E5.
- 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.
As a result, you can see the complete Birth Place column.
Practice Section
You can download the above Excel file and practice the explained methods.
Conclusion
That’s all for today. We have listed 5 methods 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. You can visit our website Exceldemy for more related articles.
i want to apply this for visible cells only. I mean i already have some results wants to filer out & then apply this code VlookupFull only for visible cells only. Pls let me know the changes i can make to accomplish this
You can try using SpecialCells(xlCellTypeVisible) property while setting the detailsLastRow object. SpecialCells(xlCellTypeVisible) will trigger Excel to consider visible cells only. I hope this edited VBA will help you to get your desired output.
I am having trouble with the following vlookup statement:
Debug.Print ActiveWorkbook.Name
Debug.Print ActiveSheet.Name
Range(“B2:B” & FinalRow).FormulaR1C1 = _
“=VLOOKUP(RC[1],wsx!R2C2:R9999C3,2,FALSE)”
First: I verify that the “Range(“B2:B” & FinalRow).FormulaR1C1″, i.e. B2:B is the correct column where I want the vlookup formula is to be plaed.
Secord: The “Debug.Print” verifies that the ActiveWorkbook and worksheet are correct.
Finally: What happens when this code line executes is that a file explorer dialog opens with title “Update Values wsx” (wsx is the variable name for the remote worksheet where the lookup values are stored) with the Filename box looking for a file to save the formula? The lookup formula is not stored and the script ends.
Hi MRBRAT2U,
Thanks for reaching us. You can use the following code to execute your desired operation.
According to your requirement, I have created a source worksheet wsx containing a list of projects with their costs up to 9999 rows.
For the results, we have enlisted some project names in the Sheet2 column and after running the code we will extract the cost values in the Cost column.
• Type the following code in your created module.
• Press F5.
Afterward, you will have the cost values extracted in the Cost column.
Good afternoon,
I begin to learn VBA for my work, Can you help me problem as my attached code, in vlookup function, variable “Filename1″ not active, inspite of VBA not warning error, Thanks you very much!
sub tham_dinh ()
Dim work_sheet As Sheets
Dim selectFiles As Variant
Dim iFileNum, iSheetNum, i As Integer
Dim iLastRowInput, iLastRowOutput As Long
Dim tieude As Integer
Dim Filename, Filename1, Filename2 As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
‘ Buoc 2:Goi phuong thuc mo nhieu file
selectFiles = Application.GetOpenFilename(filefilter:=”Excel File (*.xls*),*.xlsx*”, MultiSelect:=True)
‘ Buoc 3:Bien chay de mo tung file
For iFileNum = 1 To 2
Set wbInput = Workbooks.Open(selectFiles(iFileNum))
‘ Buoc 3: lay ten file
If iFileNum = 1 Then
Filename1 = wbInput.Name
End If
If iFileNum = 2 Then
Filename2 = wbInput.Name
End If
Next
‘ VLOOKUP COT T
Windows(Filename2).Activate
‘ Windows(Filename1).Activate
Set work_book = Workbooks(Filename1)
Range(“T3”).Select
ActiveCell.FormulaR1C1 = _
“=VLOOKUP(RC[-19],'[Filename1]Sheet1’!R3C1:R800C15,13,FALSE)”
Hello HUYNH LE TUAN,
Thank you for your question. I couldn’t understand your question properly. Could you please provide your Excel workbook? It would be easy for me to solve the code error if you send me a copy of your Excel workbook. You can send your workbook here- [email protected]