VBA: How to Use VLookUp in a Macro

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:

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.

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.

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.

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:

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.

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