Searching and Matching Lists

[sylink]

Pls help with a macro to solve the task below:

Sheet1, column A has an unfixed or undetermined range of names
starting at "A2".
Sheet2 column A has a related name list also.
For every item in sheet2 column A, search through sheet1 Column A
Where a match is found, indicate "PRESENT" in the corresponding cell
in Sheet2 column B.

 

[Ardus Petus]

In Sheet2!B2, enter:
=IF(COUNTIF(Sheet1!A:A,A2)>0,"PRESENT","")
then drag down

HTH

 

[Tom Ogilvy]

Sub CompareLists()
Dim sh1 as Worksheet, sh2 as Worksheet
Dim rng1 as Range, rng2 as Range
Dim cell as Range, res as Variant
set sh1 = Worksheets("sheet1")
set sh2 = Worksheets("sheet2")
set rng1 = sh1.Range(sh1.Cells(2,1),sh1.Cells(2,1).End(xldown))
set rng2 = sh2.Range(sh2.Cells(2,1),sh2.Cells(2,1).End(xldown))
for each cell in rng2
res = Application.Match(cell.Value,rng1,0)
if not iserror(res) then
cell.offset(0,1).Value = "Present"
end if
next
end sub

 

[sylink]

Thanks for the prompt response. I'll ur solutions a try