Comparing cells
 

I'm looking to compare values in 2 columns in two
different work sheets... If for example; If the ID in
sheet 1 A1:A10 is equal to ID in sheet 2 D1:D10, then
copy the adjacent 3 cells to Sheet 1...

Sheet 1

A B C D E F G
12
13
14

Sheet 2

D E F G
11 1 2 3
13 4 5 6
16 7 8 9
14 3 2 1

Result after Function;

Sheet 1

A B C D E F G
12
13 4 5 6
14 3 2 1

Any help would be greatly appreciated;
Thanks
Kevin

Comparing cells
 

Sub CopyIDData()
Dim rng As Range
Dim rng1 As Range
Dim cell As Range
Dim res As Variant

With Worksheets("sheet1")
Set rng = .Range(.Range("A1"), .Range("A1").End(xlDown))
End With
With Worksheets("Sheet2")
Set rng1 = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
End With

For Each cell In rng
res = Application.Match(cell, rng1, 0)
If Not IsError(res) Then
rng1(res, 2).Resize(1, 3).Copy Destination:=cell.Offset(0, 1)
End If
Next
End Sub


--
Regards,
Tom Ogilvy


wrote in message
...
I'm looking to compare values in 2 columns in two
different work sheets... If for example; If the ID in
sheet 1 A1:A10 is equal to ID in sheet 2 D1:D10, then
copy the adjacent 3 cells to Sheet 1...

Sheet 1

A B C D E F G
12
13
14

Sheet 2

D E F G
11 1 2 3
13 4 5 6
16 7 8 9
14 3 2 1

Result after Function;

Sheet 1

A B C D E F G
12
13 4 5 6
14 3 2 1

Any help would be greatly appreciated;
Thanks
Kevin