i need some help with macro. how do i compare names from b3:b50 of sheet1 to
complete listing of names(a1:a100) and employee id(b1:b100) of sheet2?
after finding a match it will output the employee id to a3:a50 of sheet1. if
it didn't find a match return blank.
ex. sheet 1 sheet 2
b3=alex a1=alex b1=001
output would be:
sheet 1
a3=001

Give this a try:

Sub terranian()
Dim lr As Long, lr2 As Long
Dim c As Range, serRng As Range, f As Range
lr = Sheets(1).Cells(Rows.Count, 2).End(xlUp).Row
lr2 = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row
Set serRng = Sheets(2).Range("A1:A" & lr2)
For Each c In Sheets(1).Range("B3:B" & lr)
Set f = serRng.Find(c.Value, LookIn:=xlValues, _
MatchCase:=False)
If Not f Is Nothing Then
f.Offset(0, 1).Copy Sheets(1).Range("A" & c.Row)
End If
Next
End Sub

Copy this code to your standard VBA module. You might want to substitute
the sheets names for the index numbers that I used, unless they are in the
correct order as written.


"louie" wrote:

> i need some help with macro. how do i compare names from b3:b50 of sheet1 to
> complete listing of names(a1:a100) and employee id(b1:b100) of sheet2?
> after finding a match it will output the employee id to a3:a50 of sheet1. if
> it didn't find a match return blank.
> ex. sheet 1 sheet 2
> b3=alex a1=alex b1=001
> output would be:
> sheet 1
> a3=001
>
>