Use of VLOOKUP in VBA 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 VBA VLOOKUP in another sheet. 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 complex dataset.

Practice Workbook

You are welcome to download the practice workbook from the link below.

VBA VLOOKUP in 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 - Excel VBA VLOOKUP in Another Worksheet

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 - Excel VBA VLOOKUP in Another Worksheet

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

2. Use VLOOKUP for a Selected cell

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 - Excel VBA VLOOKUP in Another Worksheet

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 - Excel VBA VLOOKUP in Another Worksheet

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 - Excel VBA VLOOKUP in Another Worksheet

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 - Excel VBA VLOOKUP in Another Worksheet

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 - Excel VBA VLOOKUP in Another Worksheet

Similar to this approach, fill the Birth Place column.

Result of selection cell code 3- Excel VBA VLOOKUP in Another Worksheet

3. 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 - Excel VBA VLOOKUP in Another Worksheet

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 - Excel VBA VLOOKUP in Another Worksheet

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 - Excel VBA VLOOKUP in Another Worksheet

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 VBA VLOOKUP in another sheet. 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.

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo