Dim cel As Range

Sub ConvertMaterialToId()

For Each cel In Worksheets("Sheet2").Columns(1)
If Len(cel) 0 Then
cel.Value =
WorksheetFunction.Index(Worksheets("Sheet1").Colum ns(2),
WorksheetFunction.Match(cel, Sheets("Sheet1"), 0), 1)
Else
If Len(cel) = 0 Then Exit Sub
End If
Next

End Sub

I get an error message "Subscript out of Range". What's wrong with that
piece of code?


STEVE BELL wrote:
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