Matching data

[Karen]

Hello all
The macro below will match data in sheet1 with that in Sheet2 and when
found copy three cells from the first sheet to the other. The problem
I have is when more then one match is made in that it overwrites the
previous data rather then adding to it.
I would be obliged for your help.

TIA
Karen

Sub Match()
Dim lookupRange As Range
Dim myRange As Range
Dim cell As Range
Dim myRow As Variant

Set lookupRange = Sheets("Sheet1").Range("C:C")
With Sheets("Sheet2")
Set myRange = .Range("B2:B" & .Range("B" &
Rows.Count).End(xlUp).Row)
End With
For Each cell In myRange
myRow = Application.Match(cell.Value, lookupRange, 0)
If Not IsError(myRow) Then cell.Offset(0, 2).Resize(1, 3).Value =
_
lookupRange(myRow).Offset(0, 1).Resize(1, 3).Value
Next cell

End Sub

 

[Don Guillett]

Your last row must be determined within the for/next

one way
for each c in
x=sheets("destinationsheet").cells(rows.count,"c").end(xlup).row+1
next

 

[Karen]

Don
Many thanks for your reply.
I could not get the macro to accomplish the task after incorporating
your suggestion. I was going to post my revised macro as I think that
I must have done something wrong but having left my floppy at work am
not in a position to do so.
Would you mind having a look again?

TIA
Karen