Use of VBA VLOOKUP to Find Values from Another Worksheet in Excel

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.

Before diving into the session, let’s get to know about our example dataset.

Author list - Excel VBA VLOOKUP in Another Worksheet

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.

details list - Excel VBA VLOOKUP in Another Worksheet

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 more 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.

Birth Place column - Excel VBA VLOOKUP in Another Worksheet

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.  

Module - Excel VBA VLOOKUP in Another Worksheet

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 Sub

Here we have written a SUB procedure, declared a variable ws that is a worksheet. And set the “Static Changes” in it.

Static Code

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).

Code result - Excel VBA VLOOKUP in Another Worksheet

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.

Change in static code - Excel VBA VLOOKUP in Another Worksheet

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.

Static code output 2

You will find the result. Now change the respective cell references for the rest of the values to find the results.

Static code example output - Excel VBA VLOOKUP in Another Worksheet

Read More: Excel VLOOKUP to Return Multiple Values Vertically


Similar Readings


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.

Change Selection sheet - Excel VBA VLOOKUP in Another Worksheet

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.

Record Macro

This will bring the Record Macro dialog box in front of you. Set the macro name and shortcut key to the respective fields.

Record Macro dialog box

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

 

Cell selection code

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).

Select cell - Excel VBA VLOOKUP in Another Worksheet

Save the code, then select the cell and press the shortcut key (CTRL+SHIFT+J).

Result of selection cell code

We have found the result we were looking for. Now select the next cell and press the shortcut key.

Result of selection cell code 2

Similar to this approach, fill the Birth Place column.

Result of selection cell code 3

Read More: VLOOKUP Example Between Two Sheets in Excel

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.

Again, to keep the changes understandable, we have copied the dataset to the “Entire At Once” sheet.

Entire At Once sheet - Result of selection cell code

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

Entire data vlookup code

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.

Entire data vlookup code result

We have found the birthplaces for all the authors at once.

Read More: Excel Dynamic VLOOKUP (with 3 Formulas)

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.


Further Readings

Shakil Ahmed

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

4 Comments
  1. 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

    • Reply
      Naimul Hasan Arif Aug 14, 2022 at 5:30 PM

      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.

  2. 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.
      1
      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.
      2
      • Type the following code in your created module.

      Sub vlookupvba()
      Dim FinalRow As Long
      Dim ws As Worksheet
      Set ws = Worksheets("Sheet2")
      FinalRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
      Debug.Print ActiveWorkbook.Name
      Debug.Print ActiveSheet.Name
      Range("B2:B" & FinalRow).FormulaR1C1 = "=VLOOKUP(RC[-1],wsx!R2C1:R9999C2,2,FALSE)"
      End Sub

      3
      • Press F5.
      Afterward, you will have the cost values extracted in the Cost column.
      4

Leave a reply

ExcelDemy
Logo