MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Automate a Vlookup with a macro

Posted by Shanna on January 09, 2002 7:57 AM

I want to automate a vlookup that I do quite often with a macro, if possible. First I would select the cells that I want to lookup, then the macro should:

1. Open the workbook that contains a list (Ex. C:\My Documents\My Folder\List.xls)
2. For each cell in selection, lookup the value in list (columns A:B)
3. If value is found in A, put the value found in B in the first avalaiable cell in the same row as the selected cell.
4. Next cell
5. Sort worksheet by values in new column.

I can be more specific as to cell locations, but would prefer to hardwire as little as possible. Is this possible, and if so, might anyone out there be able to help me with the code?

Thanks :-)

Posted by Scott on January 09, 2002 8:22 AM

Why don't you just use a vlookup like this:

=VLOOKUP(A1,C:\My Documents\My Folder\[Lists.xls]Sheet1'!$A$1:$B$100,2,0)

You could then sort, or record a macro to sort.

Posted by Scott on January 09, 2002 8:24 AM

Typo in formula

Use this:

=VLOOKUP(A1,'C:\My Documents\My Folder\[Lists.xls]Sheet1'!$A$1:$B$100,2,0)

Posted by Shanna on January 09, 2002 8:27 AM

That's what I've been using. I was just hoping there was a faster way since its so redundant. Oh well. Thanks anyway though:-)