#How to use vlookup in excel vba code

Here’s how you set it up so Excel will know where to get the data from. I like short names, so I’m going to call it db. You can name this workbook whatever you want. Alls you gotta do is create an object with that lookup workbook. You can have Excel run a VLookUp in a different workbook. Just substitute your sheet index and column IDs.īut here’s the real fun bit.

#How to use vlookup in excel vba code

That’s embedded in the worksheet itself, but you can also call that into your macro code by using this line: (LookUpValue, Sheets(1).Range('A:E'), ColumnToReturn, False) Here’s the basics of the VLookUp Function: 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. So… Onto some test data for a practice run. And who wants to tempt fate with copy/paste errors? What’s worse-it’s really damn easy to lose track of which column you’re copying and pasting from. You have to CTRL + F, type in your search data, scroll over to the column with your target data, copy, paste, repeat. Especially if you’re frequently referencing that spreadsheet for multiple values. If you find yourself having to reference subsets of that data frequently, it can become a giant pain in the ass. 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. In fact, if you aren’t an Excel nerd, just run. It’s probably going to be the most boring post of all time for anyone who isn’t an Excel nerd. Alright, here’s something I’ve been meaning to learn for weeks.