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