I need a simple macro that will go thru each row of a sheet1 and find each
occurance of column A in sheet 2 and for each occurance fill in the next
column of sheet 1 with the value of Column B from sheet 2.

I tried a vlookup formula which can be used to find the first occurance of
the value in Sheet 1 column A and will return the first match found in Sheet
2. The problem occurs when a second match is found, the vlookup does not see
it. In addition to the need to find the additional values associated with
the repeat numbers, the program needs to place the found value in sheet 1
for each occurance. If 3 values are found then it should fill in Col 2 with
the first found value, Col 3 with the second and so on....

Thanks in advance.

Sub EFG()
Dim sh As Worksheet
Dim rng As Range, rng1 As Range
Dim rng2 As Range, rng4 As Range
Dim cell As Range, sAddr As String
Dim rng2A As Range
Set sh = Worksheets("sheet1")
With sh
Set rng1 = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With
With Worksheets("Sheet2")
Set rng2 = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
Set rng2A = rng2(rng2.Count)
End With
Debug.Print rng1.Address, rng2.Address
For Each cell In rng1
Set rng = Nothing
Set rng = rng2.Find(what:=cell.Value, _
After:=rng2A, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)

If Not rng Is Nothing Then
sAddr = rng.Address
Do
Set rng4 = sh.Cells(cell.Row, 256).End(xlToLeft)(1, 2)
rng4.Value = rng.Offset(0, 1).Value
Set rng = rng2.FindNext(rng)
Loop While rng.Address < sAddr
End If
Next
End Sub

--
Regards,
Tom Ogilvy


"Troy Murphy" wrote in message
...
I need a simple macro that will go thru each row of a sheet1 and find each
occurance of column A in sheet 2 and for each occurance fill in the next
column of sheet 1 with the value of Column B from sheet 2.

I tried a vlookup formula which can be used to find the first occurance of
the value in Sheet 1 column A and will return the first match found in
Sheet
2. The problem occurs when a second match is found, the vlookup does not
see
it. In addition to the need to find the additional values associated with
the repeat numbers, the program needs to place the found value in sheet 1
for each occurance. If 3 values are found then it should fill in Col 2
with
the first found value, Col 3 with the second and so on....

Thanks in advance.

Many Many Thanks!
It worked perfectly! I certainly wish I knew what I was doing :-)

Troy

"Tom Ogilvy" wrote in message
...
Sub EFG()
Dim sh As Worksheet
Dim rng As Range, rng1 As Range
Dim rng2 As Range, rng4 As Range
Dim cell As Range, sAddr As String
Dim rng2A As Range
Set sh = Worksheets("sheet1")
With sh
Set rng1 = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With
With Worksheets("Sheet2")
Set rng2 = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
Set rng2A = rng2(rng2.Count)
End With
Debug.Print rng1.Address, rng2.Address
For Each cell In rng1
Set rng = Nothing
Set rng = rng2.Find(what:=cell.Value, _
After:=rng2A, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)

If Not rng Is Nothing Then
sAddr = rng.Address
Do
Set rng4 = sh.Cells(cell.Row, 256).End(xlToLeft)(1, 2)
rng4.Value = rng.Offset(0, 1).Value
Set rng = rng2.FindNext(rng)
Loop While rng.Address < sAddr
End If
Next
End Sub

--
Regards,
Tom Ogilvy


"Troy Murphy" wrote in message
...
I need a simple macro that will go thru each row of a sheet1 and find
each
occurance of column A in sheet 2 and for each occurance fill in the next
column of sheet 1 with the value of Column B from sheet 2.

I tried a vlookup formula which can be used to find the first occurance
of
the value in Sheet 1 column A and will return the first match found in
Sheet
2. The problem occurs when a second match is found, the vlookup does not
see
it. In addition to the need to find the additional values associated with
the repeat numbers, the program needs to place the found value in sheet 1
for each occurance. If 3 values are found then it should fill in Col 2
with
the first found value, Col 3 with the second and so on....

Thanks in advance.