Try this (code untested).
Caution it will error out if the value of cel is not found on Sheet1

Dim cel As Range

For Each cel In Sheets("Sheet2").Columns(1)
If Len(cel) 0 Then
cel.Value = WorksheetFunction.Index(Sheets("Sheet1").Columns(2 ), _
Match(cel, Sheets("Sheet1"), 0), 1)
ElseIf Len(cel) = 0 Then
Exit Sub
End If
End Sub

You can build a safeguard
if worksheetfunction.Countif(Sheets("Sheet1").Columns (1),cel)0 then

end if


--
steveB

Remove "AYN" from email to respond
wrote in message
ups.com...
Here is my little problem:

1st spreadsheet
===============

Column A | Column B
---------+----------
AAA(Tab) | 80001
BBB(Tab) | 80002
CCC(Tab) | 80003
XXX(Tab) | 80004
YYY(Tab) | 80005
ZZZ(Tab) | 80006

2nd spreadsheet
===============

Column A
--------
AAA
AAA
AAA
AAA
BBB
BBB
BBB
BBB
CCC
CCC
YYY
YYY
ZZZ
ZZZ
ZZZ
XXX
XXX

Expected result:
----------------

2nd spreadsheet
===============

Column A
--------
80001
80001
80001
80001
80002
80002
80002
80002
80003
80003
80005
80005
80006
80006
80006
80004
80004

I need to translate column A in the 2nd spreadsheet to numbers from the
1st spreadsheet (column B). I would appreciate any suggestion.

Regards,
koobel