Search and replace using a named array on another worksheet

[Ixtreme]

Hello,


Hopefully somebody can help me with the following:

I have a script that imports data from an external source to sheet1.
On sheet2 I have named range EMPLOYEE which holds the employee code in
columnA and the full name in columnB.
What I would like is a vba script to walk through each value in column
A on sheet1 to replace the employeecode with the fullname which can be
found on sheet2 (EMPLOYEE).

(EMPLOYEE is sorted on employeecode).

I know how to do this with vlookup, however I would like to have this
automated.

Thanks,

Mark

 

[Guest]

Sub ReplaceWithName()
Dim rng as Range, r as Range, cell as Range
Dim res as Variant
set rng = ActiveWorkbook.Names("EMPLOYEE").ReferstoRange

with worksheets("sheet1")
set r = .Range(.Cells(2,1),.Cells(rows.count,1).End(xlup))
End with

for each cell in r
res = application.Vlookup(cell,rng,2,0)
if not iserror(res) then
cell.Value = res
end if
Next

End Sub

 

[Ixtreme]

Thanks (again) Tom !!