Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Values to matching labels
i've got a list of possible labels in column A, and a list of present
labels in column J. Now I want to copy the data from column J to column b in the row corresponding to the appropriate label (in column A). Here's what i have so far Sub Macro1() Dim rngA As Range Dim rngB As Range Dim A As Range Dim LrowA As Long Dim LrowB As Long LrowA = Cells(Rows.Count, "I").End(xlDown).Row Set rngA = Range("I7:I" & LrowA) LrowB = Cells(Rows.Count, "A").End(xlDown).Row Set rngB = Range("A7:A" & LrowB) For Each A In rngA myCell = A.Value A.Select ActiveCell.Offset(0, 1).Activate Selection.Copy rngB.Select Selection.Find(What:=A.Value, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(0, 1).Activate Selection.Paste Next A Application.DisplayAlerts = True End Sub i'm pretty new to this, so i don't even know if this is the best way to try and do this, but it keeps flagging the Selection.Paste line with a runtime error 438, Object doesn't support this property or method. Any ideas? oh, and there's some other code that i left out that just parses and transposes the data, that's why i set the ranges where they are. it's working so i left it out, but i can put it in if it'd help |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Values to matching labels
ok, well, i feel a little dumb about that one, but i've fixed it now.
another problem i'm having now though, is the macro just keeps going, forever, until i ctrl shift break it keeps copying and pasting the same info over and over again. i'm pretty sure i haven't just made up a command on this one like i did on that first question, but who knows. Can anyone tell me how to end this loop that i seem to have gotten myself stuck in |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Values to matching labels
Here is a loop within a loop:
Sub Macro1() Dim rngA As Range Dim rngB As Range Dim A As Range Dim B As Range Dim LrowA As Long Dim LrowB As Long LrowA = Cells(Rows.Count, "I").End(xlUp).Row Set rngA = Range("I7:I" & LrowA) LrowB = Cells(Rows.Count, "A").End(xlUp).Row Set rngB = Range("A7:A" & LrowB) For Each A In rngA For Each B In rngB If A.Value = B.Value Then B.Offset(0, 1).Value = A.Offset(0, 1).Value End If Next B Next A Application.DisplayAlerts = True End Sub Mike F "Craig" wrote in message oups.com... i've got a list of possible labels in column A, and a list of present labels in column J. Now I want to copy the data from column J to column b in the row corresponding to the appropriate label (in column A). Here's what i have so far Sub Macro1() Dim rngA As Range Dim rngB As Range Dim A As Range Dim LrowA As Long Dim LrowB As Long LrowA = Cells(Rows.Count, "I").End(xlDown).Row Set rngA = Range("I7:I" & LrowA) LrowB = Cells(Rows.Count, "A").End(xlDown).Row Set rngB = Range("A7:A" & LrowB) For Each A In rngA myCell = A.Value A.Select ActiveCell.Offset(0, 1).Activate Selection.Copy rngB.Select Selection.Find(What:=A.Value, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(0, 1).Activate Selection.Paste Next A Application.DisplayAlerts = True End Sub i'm pretty new to this, so i don't even know if this is the best way to try and do this, but it keeps flagging the Selection.Paste line with a runtime error 438, Object doesn't support this property or method. Any ideas? oh, and there's some other code that i left out that just parses and transposes the data, that's why i set the ranges where they are. it's working so i left it out, but i can put it in if it'd help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding Most Recent Values in Col1 -- Summing Matching Values | Excel Discussion (Misc queries) | |||
How do I copy info while matching labels? | Excel Worksheet Functions | |||
Matching and Copying entries | Excel Discussion (Misc queries) | |||
Matching and copying problem | Excel Programming | |||
Fill values into a listbox matching selected values from a combobox | Excel Programming |