Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Col A and Col M, if Match, Copy Col N to Col E
I accidentally posted this in the Excel-Functions group; sorry all. Anyway,
I'm trying to get this macro to compare values in Column A and Column M, starting in Cell(2, 13), and if there is a match, copy the value from Column N (that corresponds to Column M) into Column E. For instance, 4/1/2008 is in Cell A1 (and the dates go down consecutively). I have 4/6/2008 in M2 and 5,000,000 in N2. How can I get 5,000,000 into E6 (A6 contains 4/6/2008)? Confused yet? I am. Sub MatchAandM() Dim Lrow As Long Dim Rng As Range, i As Range, xRng As Range Lrow = Range("A65536").End(xlUp).Row Set Rng = Range(Cells(2, 13), Cells(Lrow, 2)) For Each i In Rng Set xRng = Rng.Find(What:=i.Offset(0, -1).Value, _ LookIn:=xlValues, MatchCase:=False) If xRng Is Nothing Then 'Else i.Offset(0, -1).Copy i.Offset(0, 1) End If Next i End Sub Thanks for the help; I truly appreciate it. Regards, Ryan-- -- RyGuy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Col A and Col M, if Match, Copy Col N to Col E
Hi,
I think you said if A2=M2 then make E2 equal to N2 so right click the sheet tab, view code and paste this in Sub MatchAandM() Dim Lrow As Long Dim Rng As Range, i As Range, xRng As Range Lrow = Cells(Rows.Count, "A").End(xlUp).Row Set Rng = Range("A2:A" & Lrow) For Each i In Rng If i.Value = i.Offset(0, 12).Value Then i.Offset(0, 4).Value = i.Offset(0, 13).Value End If Next End Sub Mike "ryguy7272" wrote: I accidentally posted this in the Excel-Functions group; sorry all. Anyway, I'm trying to get this macro to compare values in Column A and Column M, starting in Cell(2, 13), and if there is a match, copy the value from Column N (that corresponds to Column M) into Column E. For instance, 4/1/2008 is in Cell A1 (and the dates go down consecutively). I have 4/6/2008 in M2 and 5,000,000 in N2. How can I get 5,000,000 into E6 (A6 contains 4/6/2008)? Confused yet? I am. Sub MatchAandM() Dim Lrow As Long Dim Rng As Range, i As Range, xRng As Range Lrow = Range("A65536").End(xlUp).Row Set Rng = Range(Cells(2, 13), Cells(Lrow, 2)) For Each i In Rng Set xRng = Rng.Find(What:=i.Offset(0, -1).Value, _ LookIn:=xlValues, MatchCase:=False) If xRng Is Nothing Then 'Else i.Offset(0, -1).Copy i.Offset(0, 1) End If Next i End Sub Thanks for the help; I truly appreciate it. Regards, Ryan-- -- RyGuy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Col A and Col M, if Match, Copy Col N to Col E
Sub MatchAandM()
Dim Lrow As Long Dim RowCount As Long Dim xRng As Range Lrow = Range("A" & Rows.Count).End(xlUp).Row For RowCount = 2 To Lrow FindVal = Range("A" & RowCount) Set xRng = Columns("M:M").Find(What:=FindVal, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If Not xRng Is Nothing Then xRng.Offset(0, 1).Copy xRng.Offset(0, -8) End If Next RowCount End Sub "ryguy7272" wrote: I accidentally posted this in the Excel-Functions group; sorry all. Anyway, I'm trying to get this macro to compare values in Column A and Column M, starting in Cell(2, 13), and if there is a match, copy the value from Column N (that corresponds to Column M) into Column E. For instance, 4/1/2008 is in Cell A1 (and the dates go down consecutively). I have 4/6/2008 in M2 and 5,000,000 in N2. How can I get 5,000,000 into E6 (A6 contains 4/6/2008)? Confused yet? I am. Sub MatchAandM() Dim Lrow As Long Dim Rng As Range, i As Range, xRng As Range Lrow = Range("A65536").End(xlUp).Row Set Rng = Range(Cells(2, 13), Cells(Lrow, 2)) For Each i In Rng Set xRng = Rng.Find(What:=i.Offset(0, -1).Value, _ LookIn:=xlValues, MatchCase:=False) If xRng Is Nothing Then 'Else i.Offset(0, -1).Copy i.Offset(0, 1) End If Next i End Sub Thanks for the help; I truly appreciate it. Regards, Ryan-- -- RyGuy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Col A and Col M, if Match, Copy Col N to Col E
Joel, its close. I realize now that didn't describe the problem well enough.
The dates are arranged in descending order in Col A. 4/1/2008 is in A1, 4/6/2008 is in A6 and 5/1/2008 is in A31. 4//2008 is in M2 and 5/1/2008 is in M3. 5 is in N2 and 2 is in N3. I was hoping to match the dates in Col M to those in Col A, and if there is a match, copy/paste the values in Col N to the corresponding row in Col E. Thus, E6 would contain 5 and E31 would contain 2. I'll try to fiddle with your code, but I'm a bit lost with this one. If you know how to modify the code to do what I was hoping to do, please make the change and send it along... -- RyGuy "Joel" wrote: Sub MatchAandM() Dim Lrow As Long Dim RowCount As Long Dim xRng As Range Lrow = Range("A" & Rows.Count).End(xlUp).Row For RowCount = 2 To Lrow FindVal = Range("A" & RowCount) Set xRng = Columns("M:M").Find(What:=FindVal, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If Not xRng Is Nothing Then xRng.Offset(0, 1).Copy xRng.Offset(0, -8) End If Next RowCount End Sub "ryguy7272" wrote: I accidentally posted this in the Excel-Functions group; sorry all. Anyway, I'm trying to get this macro to compare values in Column A and Column M, starting in Cell(2, 13), and if there is a match, copy the value from Column N (that corresponds to Column M) into Column E. For instance, 4/1/2008 is in Cell A1 (and the dates go down consecutively). I have 4/6/2008 in M2 and 5,000,000 in N2. How can I get 5,000,000 into E6 (A6 contains 4/6/2008)? Confused yet? I am. Sub MatchAandM() Dim Lrow As Long Dim Rng As Range, i As Range, xRng As Range Lrow = Range("A65536").End(xlUp).Row Set Rng = Range(Cells(2, 13), Cells(Lrow, 2)) For Each i In Rng Set xRng = Rng.Find(What:=i.Offset(0, -1).Value, _ LookIn:=xlValues, MatchCase:=False) If xRng Is Nothing Then 'Else i.Offset(0, -1).Copy i.Offset(0, 1) End If Next i End Sub Thanks for the help; I truly appreciate it. Regards, Ryan-- -- RyGuy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Col A and Col M, if Match, Copy Col N to Col E
I think you need only two changes
1) Data in column A starts in row 1 not 2 from For RowCount = 2 To Lrow to For RowCount = 1 To Lrow 2) You want the data in column N to move to the row matching the data in Column A (not column M) from xRng.Offset(0, 1).Copy xRng.Offset(0, -8) to xRng.Offset(0, 1).Copy Range("E" & RowCount) "ryguy7272" wrote: Joel, its close. I realize now that didn't describe the problem well enough. The dates are arranged in descending order in Col A. 4/1/2008 is in A1, 4/6/2008 is in A6 and 5/1/2008 is in A31. 4//2008 is in M2 and 5/1/2008 is in M3. 5 is in N2 and 2 is in N3. I was hoping to match the dates in Col M to those in Col A, and if there is a match, copy/paste the values in Col N to the corresponding row in Col E. Thus, E6 would contain 5 and E31 would contain 2. I'll try to fiddle with your code, but I'm a bit lost with this one. If you know how to modify the code to do what I was hoping to do, please make the change and send it along... -- RyGuy "Joel" wrote: Sub MatchAandM() Dim Lrow As Long Dim RowCount As Long Dim xRng As Range Lrow = Range("A" & Rows.Count).End(xlUp).Row For RowCount = 2 To Lrow FindVal = Range("A" & RowCount) Set xRng = Columns("M:M").Find(What:=FindVal, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If Not xRng Is Nothing Then xRng.Offset(0, 1).Copy xRng.Offset(0, -8) End If Next RowCount End Sub "ryguy7272" wrote: I accidentally posted this in the Excel-Functions group; sorry all. Anyway, I'm trying to get this macro to compare values in Column A and Column M, starting in Cell(2, 13), and if there is a match, copy the value from Column N (that corresponds to Column M) into Column E. For instance, 4/1/2008 is in Cell A1 (and the dates go down consecutively). I have 4/6/2008 in M2 and 5,000,000 in N2. How can I get 5,000,000 into E6 (A6 contains 4/6/2008)? Confused yet? I am. Sub MatchAandM() Dim Lrow As Long Dim Rng As Range, i As Range, xRng As Range Lrow = Range("A65536").End(xlUp).Row Set Rng = Range(Cells(2, 13), Cells(Lrow, 2)) For Each i In Rng Set xRng = Rng.Find(What:=i.Offset(0, -1).Value, _ LookIn:=xlValues, MatchCase:=False) If xRng Is Nothing Then 'Else i.Offset(0, -1).Copy i.Offset(0, 1) End If Next i End Sub Thanks for the help; I truly appreciate it. Regards, Ryan-- -- RyGuy |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Col A and Col M, if Match, Copy Col N to Col E
That's it! Thank you so much!!
Ryan-- -- RyGuy "Joel" wrote: I think you need only two changes 1) Data in column A starts in row 1 not 2 from For RowCount = 2 To Lrow to For RowCount = 1 To Lrow 2) You want the data in column N to move to the row matching the data in Column A (not column M) from xRng.Offset(0, 1).Copy xRng.Offset(0, -8) to xRng.Offset(0, 1).Copy Range("E" & RowCount) "ryguy7272" wrote: Joel, its close. I realize now that didn't describe the problem well enough. The dates are arranged in descending order in Col A. 4/1/2008 is in A1, 4/6/2008 is in A6 and 5/1/2008 is in A31. 4//2008 is in M2 and 5/1/2008 is in M3. 5 is in N2 and 2 is in N3. I was hoping to match the dates in Col M to those in Col A, and if there is a match, copy/paste the values in Col N to the corresponding row in Col E. Thus, E6 would contain 5 and E31 would contain 2. I'll try to fiddle with your code, but I'm a bit lost with this one. If you know how to modify the code to do what I was hoping to do, please make the change and send it along... -- RyGuy "Joel" wrote: Sub MatchAandM() Dim Lrow As Long Dim RowCount As Long Dim xRng As Range Lrow = Range("A" & Rows.Count).End(xlUp).Row For RowCount = 2 To Lrow FindVal = Range("A" & RowCount) Set xRng = Columns("M:M").Find(What:=FindVal, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If Not xRng Is Nothing Then xRng.Offset(0, 1).Copy xRng.Offset(0, -8) End If Next RowCount End Sub "ryguy7272" wrote: I accidentally posted this in the Excel-Functions group; sorry all. Anyway, I'm trying to get this macro to compare values in Column A and Column M, starting in Cell(2, 13), and if there is a match, copy the value from Column N (that corresponds to Column M) into Column E. For instance, 4/1/2008 is in Cell A1 (and the dates go down consecutively). I have 4/6/2008 in M2 and 5,000,000 in N2. How can I get 5,000,000 into E6 (A6 contains 4/6/2008)? Confused yet? I am. Sub MatchAandM() Dim Lrow As Long Dim Rng As Range, i As Range, xRng As Range Lrow = Range("A65536").End(xlUp).Row Set Rng = Range(Cells(2, 13), Cells(Lrow, 2)) For Each i In Rng Set xRng = Rng.Find(What:=i.Offset(0, -1).Value, _ LookIn:=xlValues, MatchCase:=False) If xRng Is Nothing Then 'Else i.Offset(0, -1).Copy i.Offset(0, 1) End If Next i End Sub Thanks for the help; I truly appreciate it. Regards, Ryan-- -- RyGuy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare Col A and Col M, if Match, Copy Col N to Col E | Excel Worksheet Functions | |||
Compare col and match then copy and paste | Excel Discussion (Misc queries) | |||
Compare 2 Ranges, Copy/Paste Row on Match | Excel Programming | |||
Compare 2 Ranges, Copy/Paste Row on Match | Excel Programming | |||
Compare 2 Ranges, Copy/Paste Row on Match | Excel Programming |