Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help on Matching and Copying Values In Two Different Sheets
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help on Matching and Copying Values In Two Different Sheets
sylink wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help on Matching and Copying Values In Two Different Sheets
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. Rob wrote: sylink wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matching Columns in work sheets and copying both rows to new | Excel Discussion (Misc queries) | |||
Copying as absolute values across multiple sheets | Excel Discussion (Misc queries) | |||
Copying values between sheets | Excel Programming | |||
Copying Values to matching labels | Excel Programming | |||
Fill values into a listbox matching selected values from a combobox | Excel Programming |