Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare and copy dose nothing
Can someone point me in the right direction, I can get this to work
when I copy the entire row but I only want to copy a single cell to a cell on the main page. Sheet1 is used as a part list with prices. Sub copyData() Dim rng1 As Range, rng2 As Range Dim cell As Range, rw As Long Dim sh As Worksheet With Worksheets("Sheet1") Set rng1 = .Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp)) End With With Worksheets("node 1&2") Set rng2 = .Range(.Cells(1, "B"), .Cells(.Rows.Count, "B").End(xlUp)) End With rw = 1 For Each cell In rng2 If cell.Value = rng1.Cells(Rows.Count, "A").Value Then rng1.Cells(Rows.Count, "E").Copy Destination:=rng2.Cells(Rows.Count, "E") rw = rw + 1 End If Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare and copy dose nothing
Any help at all?
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare and copy dose nothing
I'm not sure what you're doing.
Are you trying to look for a match for each cell in rng2 and then copy the value in column E to the matching row in sheet1? If yes, maybe.... Option Explicit Sub copyData() Dim Rng1 As Range Dim Rng2 As Range Dim myCell As Range Dim res As Variant With Worksheets("Sheet1") Set Rng1 = .Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp)) End With With Worksheets("node 1&2") Set Rng2 = .Range(.Cells(1, "B"), .Cells(.Rows.Count, "B").End(xlUp)) End With For Each myCell In Rng2.Cells res = Application.Match(myCell.Value, Rng1, 0) If IsError(res) Then 'no matching value Else myCell.Offset(0, 3).Copy _ Destination:=Rng1(res).Offset(0, 4) End If Next myCell End Sub But I'm really not sure if I copied the right cell--since one sheet has the key in column A and the other in column B. You may have to fiddle with this line: myCell.Offset(0, 3).Copy _ Destination:=Rng1(res).Offset(0, 4) ..offset(0,3) from column B gives me column E. ..offset(0,4) from column A gives me column E. kwl wrote: Can someone point me in the right direction, I can get this to work when I copy the entire row but I only want to copy a single cell to a cell on the main page. Sheet1 is used as a part list with prices. Sub copyData() Dim rng1 As Range, rng2 As Range Dim cell As Range, rw As Long Dim sh As Worksheet With Worksheets("Sheet1") Set rng1 = .Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp)) End With With Worksheets("node 1&2") Set rng2 = .Range(.Cells(1, "B"), .Cells(.Rows.Count, "B").End(xlUp)) End With rw = 1 For Each cell In rng2 If cell.Value = rng1.Cells(Rows.Count, "A").Value Then rng1.Cells(Rows.Count, "E").Copy Destination:=rng2.Cells(Rows.Count, "E") rw = rw + 1 End If Next End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how dose filter works to last cell in the column | Excel Discussion (Misc queries) | |||
excel file dose not open | Excel Discussion (Misc queries) | |||
why dose excel return invalid web query | Excel Discussion (Misc queries) | |||
options under tools dose not come up | Excel Worksheet Functions | |||
How can I estimate EC50 in a dose-response graph? | Charts and Charting in Excel |