Help on Matching and Copying Values In Two Different Sheets

[sylink]

The code below is an attempt to match column A sheet1 and column A
sheet2. Where a match exist, then copy the cell content in column F
sheet1 to the corresponding cell in column F sheet2. I guess i have
problem with the syntax on this line:
sh2.Cells.Offset(0, 6).Value = sh1.Cells.Offset(0, 6).Value

The entire code is shown below:

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

sh2.Cells.Offset(0, 6).Value = sh1.Cells.Offset(0, 6).Value

End If
Next


Thanks in advance

 

[Rob]

The code below is an attempt to match column A sheet1 and column A
sheet2. Where a match exist, then copy the cell content in column F
sheet1 to the corresponding cell in column F sheet2. I guess i have
problem with the syntax on this line:
sh2.Cells.Offset(0, 6).Value = sh1.Cells.Offset(0, 6).Value

The entire code is shown below:

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

sh2.Cells.Offset(0, 6).Value = sh1.Cells.Offset(0, 6).Value

End If
Next


Thanks in advance

It doesn't look like you are referring to the cell offsets correctly.
Try this instead:

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, sh1.Cells(cell.Row, 1), 0)
If Not IsError(res) Then

cell.Cells(1, 6) = sh1.Cells(cell.Row, 6)

End If
Next

 

[sylink]

Thanks for the ealier response.
however the macro is not able match row sequence in sheet2 properly.
For instance, if the equivalent content of sht1,cell (2,6) is located
in sht2,cell(4,6), the code will write to sht2,cell (2,6) instead of
sht2, cell(4,6). Pls kindly effect this minor adjustment.