Help on Matching and Copying Values In Two Different Sheets

S

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
 
R

Rob

sylink said:
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
 
S

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top