Hi, This seems simple but I am not able to figure it out. I would appreciate
any help.
Please see the example below.

This is the data that I have:
Col A Col B
29890
Price
20010102 48.625
20010103 47.875
20010104 47.875
20010105 48.6875
66157
Price
20010102 28.9375
20010103 30.8125
20010104 31.9375
20010105 30.6875

This is the array im trying to match to (It is on Sheet2):
29890 BP
47896 JPM
66157 USB
69032 MWD

What I need is to copy BP beside 29890 and USB beside 66157 in the data
without changing anything else in column B. This is a small example, my files
contain about 40,000 rows of data that I need to match with their ID numbers.

My data needs to look something like this.

Col A Col B
29890 BP
Price
20010102 48.625
20010103 47.875
20010104 47.875
20010105 48.6875
66157 USB
Price
20010102 28.9375
20010103 30.8125
20010104 31.9375
20010105 30.6875

Hello,

Perhaps something like the following will work:

Sub foo()
On Error GoTo errHandle
Range(Sheets(1).Cells(1, 1), _
Sheets(1).Cells(65536, 1).End(xlUp)).Offset(, 1). _
SpecialCells(xlBlanks).FormulaR1C1 = _
"=VLOOKUP(RC[-1],Sheet2!R1C1:R4C2,2,1)"
'Convert to Values
With Range(Sheets(1).Cells(1, 2), _
Sheets(1).Cells(65536, 2).End(xlUp))
.Value = .Value
End With
errHandle:
End Sub

You might need to run it a few times if you have a large number of
non-contiguous cells:
http://support.microsoft.com/default...b;en-us;832293

And, your list on Sheet2 looked like it was sorted in ascending order, so
the Vlookup() function I used passes True as the final argument. If it's not,
pass False as the final argument.

Regards,
Nate Oliver

You might need to run it a few times if you have a large number of
non-contiguous cells:
http://support.microsoft.com/default...b;en-us;832293

Sorry, running it a few times isn't the answer, use the advice provided at
the link:

WORKAROUND
To work around this behavior, you may want to create a looping structure in
your VBA macro that handles less than the maximum 8,192 cells.

I.e., pass the Vlookup() function to smaller ranges over several passes.

Regards,
Nate Oliver