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
Hi,
Instead of looping through the cells, you can hard-code the first cell with vlookup formula and use auto fill instead.
LikeLike
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.
LikeLiked by 1 person
That’s a really good idea–thanks ghostie192!
LikeLiked by 1 person
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. ^^
LikeLiked by 1 person
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?
LikeLike
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.
LikeLiked by 1 person