VBA: How to Use VLookUp in a Macro

Header

Alright, here’s something I’ve been meaning to learn for weeks. It’s probably going to be the most boring post of all time for anyone who isn’t an Excel nerd. In fact, if you aren’t an Excel nerd, just run. Run far, far away.

VLookUp in a VBA script.

Why use VLookUp? Let’s say you’ve got a giant spreadsheet—this is generally only preferable if you’re dealing with a monstrous 100+ row sheet with lots of columns. If you find yourself having to reference subsets of that data frequently, it can become a giant pain in the ass. Especially if you’re frequently referencing that spreadsheet for multiple values. You have to CTRL + F, type in your search data, scroll over to the column with your target data, copy, paste, repeat.

What’s worse—it’s really damn easy to lose track of which column you’re copying and pasting from. And who wants to tempt fate with copy/paste errors?

So… Onto some test data for a practice run. I’ve got names in column A. I can use VLookup to get Excel to look through columns in the spreadsheet for a specific name and return the value in the cell one column over.

Here’s the basics of the VLookUp Function:

Example

That’s embedded in the worksheet itself, but you can also call that into your macro code by using this line:

Application.WorksheetFunction.VLookup(LookUpValue, Sheets(1).Range("A:E"), ColumnToReturn, False)

Just substitute your sheet index and column IDs.

But here’s the real fun bit. You can have Excel run a VLookUp in a different workbook. Alls you gotta do is create an object with that lookup workbook.

Workbook_VLookUp

You can name this workbook whatever you want. I like short names, so I’m going to call it db. Here’s how you set it up so Excel will know where to get the data from.

Dim db As Workbook
Set db = Workbook.Open("C:\Your Filepath.xls", True, True)

That second “True” opens a Read-Only version. For people like me who live in terror of irrecovably screwing up spreadsheets, this is a nice option.

Now, when you start juggling information between spreadsheets, it’s a good idea to stash the active workbook in an easy-access variable too. Make sure to do this before you open the lookup workbook or it’ll get really wonky.

Dim sb As Workbook
Set sb = ActiveWorkbook

For reference, this is what my active workbook looks like. I named the spreadsheet I’m using “VLookUpTester” to make my life a little easier later.

Macro_Workbook

Now you can have Excel figure out what the last row in Column A is by activating that workbook and using the End(xlUp) trick.

sb.Activate
r = Cells(Rows.Count, 1).End(xlUp).Row

Then you can run a loop to go through each of those values and look them up in the other workbook (in this case, columns A through E).

For x = 2 To r
Temp = Application.WorksheetFunction.VLookup(Cells(x, 1).Value, db.Sheets(1).Range("A:E"), 4, False)
sb.Sheets("VLookUpTester").Cells(x, 2).Value = Temp
Next x

That makes Excel do this for every row in your active workbook.

Walkthrough

I know what you’re thinking. What if I try to look up a character that doesn’t exist?

I’ll tell you exactly what happens. You get this extremely vague ass error:

Error

Not helful, Excel. Not helpful.

Here’s where On Error Resume comes to the rescue. So, I want my script to just skip any value it can’t find. When you get this Run-Time error, it halts everything. Unless you prepare it to hit the error in advance.

On Error Resume tells Excel that the next line may cause an error, and if it does, you just want it to skip to the step after that.

OnErrorResume

Presto. Script keeps plowing through the loop. It’s actually a suprizingly compact script too. Just 15 lines.

Here’s the script altogether:

Sub LookUpExt()

Dim Temp As String
Dim destbook As Workbook
Set destbook = ActiveWorkbook
Dim srcebook As Workbook
Set srcebook = Workbooks.Open("C:\Users\Desktop\VlookupTester.xlsx", True, True)

destbook.Activate
r = Cells(Rows.Count, 1).End(xlUp).Row

For x = 2 To r
On Error Resume Next
Temp = Application.WorksheetFunction.VLookup(Cells(x, 1).Value, srcebook.Sheets(1).Range("A:E"), 4, False)
destbook.Sheets("VLookUpTester").Cells(x, 2).Value = Temp
Temp = ""
Next x

End Sub

7 Replies to “VBA: How to Use VLookUp in a Macro”

  1. This will eliminate the need for:
    1.) error handling
    2.) your code will be shorter. Hard-coding the formula can be done as below:
    ActiveCell.Formula = “=VLOOKUP(lookupvalue,'[filename.xlxs]Sheetname’!start table array:$end table array,column index,true/false)”
    3. Auto fill will return N/A instead of failing if the lookup does not exist.

    Liked by 1 person

  2. No problem!
    If you are worried that autofill will stop the moment the adjacent cell is a blank, you can use variables for the range ^^

    I normally do it this way for autofill:

    Selection.AutoFill Destination:=Range(“B2:B” & Range(“A” & Rows.Count).End(xlUp).Row)

    Change the B2:B to the column that you want to fill the formula.

    The “A & Rows counts ” uses column A as a reference for the range of cells to fill.
    This will force autofill to move past blank cells. ^^

    Liked by 1 person

    1. That’s ingenious because then someone else opening up the spreadsheet will be able to see the formula when they click into the cell, yeah? So they’d know where the VLookUp value is coming from?

      Like

      1. Yes! In the event that there is a need to change the lookup, its easier to change it in the macro, or user can directly change it in the cell and autofill manually from there.

        Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: