Problem With the Use of Find Function

[sylink]

The codes below is meant to match the values in columns A of sheet1
and sheet2. And where a match is obtained, corresponding value in
sheet1, column B is copied to sheet2 column B.

The codes, however, appear to not be looping properly. There appear
repetitions of the first value found in sheet1. Pls what is missing
here?

********************************************************************************
Dim MyName As String
Dim Z As Integer
Z = 2

MyName = Sheets("Sheet2").Range("A" & Z).Value
Do While Sheets("Sheet2").Range("A" & Z).Value <> ""

Sheets("Sheet1").Cells.Find(What:=MyName, LookAt:=xlPart,
MatchCase:=False).Activate

Sheets("Sheet2").Range("B" & Z).Value = ActiveCell.Offset(0, 1)


Z = Z + 1

Loop

***********************************************************************************

 

[Tom Ogilvy]

One problem is you never change the value of MyName. You need to set it
inside the loop

Sub ABC()
Dim MyName As String
Dim Z As Integer
Dim rng as Range, rng1 as Range
Dim rng2 as Range
Z = 2

set rng =Sheets("Sheet1").Columns(1).Cells
set rng1 = rng(rng.rows.count)
Do While Sheets("Sheet2").Range("A" & Z).Value <> ""
MyName = Sheets("Sheet2").Range("A" & Z).Value
set rng2 = rng.Find(What:=MyName, After:=rng1, _
LookAt:=xlPart,MatchCase:=False)
if not rng2 is nothing then
Sheets("Sheet2").Range("B" & Z).Value = rng2.Offset(0, 1)
End if

Z = Z + 1

Loop
End Sub

 

[sylink]

Tom,
Ur soln is simply great. Thumbs up!

Sylink