Excel/Excel2007 VBA - Vlookup?
QUESTION: Hi Tom,
I’m using Excel 2007 – VBA. I think this calls for VLookup, but it seems that Col_Index_Num will only return one column and I need 4 columns. I could maybe run it 4 times, but with 50,000+ rows, it would be quite a grind.
Column A of Wb2Sh1 contains “ItemNumber” and columns E:H contain “prices”. Column A of Wb1Sh1 also contains “ItemNumber”. When the code finds a matching “ItemNumber”, I want it to copy from columns E:H (Wb2Sh1) to columns B:E (of Wb1Sh1). I’ve got both “ItemNumber” columns in GeneralNumber format. Both worksheets have a header row. Both worksheets also have other columns that have data, but I only mentioned the relevant ones here. I hope you can help.
Assume wb2sh1 and wb1sh1 are sheet names in the same workbook
B2 of wb1sh1: =iferror(vlookup($A2,wb2sh1!$A$2:$H$51000,columns()+4,False),"")
drag fill the formula to cell E2
now select B2:E2 and drag fill down to the last row with data in column A.
I can't say whether it will be quite a grind or not.
If this is a one time operation, then select B:E and copy, then do a paste special and select values to replace the formulas with the values returned.
[an error occurred while processing this directive]---------- FOLLOW-UP ----------
QUESTION: Thanks Tom
I was looking for VBA script. This will be part of an existing macro.
However, I did try pasting your formula in b2 (just to see), and Excel didn't like it. I'm pretty sure it objected to "columns()". I tried changing it to columns(e) (and also other column letters), and the program accepted it but I got no results.
that was a typo (sorry)
columns() should have been column(). Also the +4 should be +3
B2 of wb1sh1: =IFERROR(VLOOKUP($A2,wb2sh1!$A$2:$H$51000,COLUMN()+3,FALSE),"")
If I was going to do it in VBA, I probably would paste in the formula in all the cells and then replace the results.
A simple example would be
.formula = "=iferror(vlookup($A2,wb2sh1!$A$2:$H$51000,column()+3,False),"""")"
.Formula = .value
As a test, I select B2 on wb1sh1 went to the name box and typed in
this selected all the cells in the specified range
whent to the formula bar and enter =IFERROR(VLOOKUP($A2,wb2sh1!$A$2:$H$51000,COLUMN()+3,FALSE),"")
and finished with Ctrl+Enter. It took about a minute and a half to fill and calculate the formulas. The results were still formulas
I then ran the above macro and it took a little less than a minute to apply the formulas and replace the calculated values with the returned values. So the results were numeric constants.
I didn't have any other formulas in the workbook. If I did, those could cause things to run slower.
You might have been thinking of a macro like this one that copies all 4 columns at once
Dim sh As Worksheet, cell As Range, r2 As Range
Dim rng As Range
Set sh = Worksheets("wb2sh1")
Set r2 = sh.Range("A1", sh.Cells(sh.Rows.Count, 1).End(xlUp))
For Each cell In Range("A2:A51000")
Set rng = r2.Find(What:=cell, _
If Not rng Is Nothing Then
rng.Offset(0, 4).Resize(1, 4).Copy cell.Offset(0, 1)
I had to cut that off after about 3 minutes and it was only at about row 3500. The answers were correct, but it was really really slow compared to the macro above or even the manual approach that I described.
It loops through the cells in column A of wb1sh1 (51000 entries). for each cell, it Finds the match in column A of wb2sh1 (51000 entries) and then copies the 4 columns from that row back to wb1sh1 and pastes them next to the cell being processed.