repeat macro formula to all cells
i have data spread across 6 columns and 300 rows. I have written a macro that
moves data from row 3 to row 1. I want to repeat the macro so that it performs the move for row 6 to row 4, row 9 to line 7 etc. Do i have to paste and copy the macro or is there a way of writing it only once so that it repeats till the end of the range? |
For i = 3 to 303 Cells(i,"A").Entirerow.copy destination:=Cells(i-2,"A") Next i -- HTH RP (remove nothere from the email address if mailing direct) "dave glynn" wrote in message ... i have data spread across 6 columns and 300 rows. I have written a macro that moves data from row 3 to row 1. I want to repeat the macro so that it performs the move for row 6 to row 4, row 9 to line 7 etc. Do i have to paste and copy the macro or is there a way of writing it only once so that it repeats till the end of the range? |
Hi Bob,
Many thanks. What value(s) do Isubstitute for "A"? Dave "Bob Phillips" wrote: For i = 3 to 303 Cells(i,"A").Entirerow.copy destination:=Cells(i-2,"A") Next i -- HTH RP (remove nothere from the email address if mailing direct) "dave glynn" wrote in message ... i have data spread across 6 columns and 300 rows. I have written a macro that moves data from row 3 to row 1. I want to repeat the macro so that it performs the move for row 6 to row 4, row 9 to line 7 etc. Do i have to paste and copy the macro or is there a way of writing it only once so that it repeats till the end of the range? |
Dave,
You can leave it as A, that just signifies the column, but as you copy the whole row it doesn't matter. -- HTH RP (remove nothere from the email address if mailing direct) "dave glynn" wrote in message ... Hi Bob, Many thanks. What value(s) do Isubstitute for "A"? Dave "Bob Phillips" wrote: For i = 3 to 303 Cells(i,"A").Entirerow.copy destination:=Cells(i-2,"A") Next i -- HTH RP (remove nothere from the email address if mailing direct) "dave glynn" wrote in message ... i have data spread across 6 columns and 300 rows. I have written a macro that moves data from row 3 to row 1. I want to repeat the macro so that it performs the move for row 6 to row 4, row 9 to line 7 etc. Do i have to paste and copy the macro or is there a way of writing it only once so that it repeats till the end of the range? |
Another option for the Entire Row might be:
Dim R As Long 'Row For R = 3 To 303 Rows(R).Copy Rows(R - 2) Next R performs the move for row 6 to row 4, row 9 to line 7 etc.... Not sure, but if you really meant to copy every third row, consider something like: For R = 6 To 303 Step 3 -- Dana DeLouis Win XP & Office 2003 "Bob Phillips" wrote in message ... Dave, You can leave it as A, that just signifies the column, but as you copy the whole row it doesn't matter. -- HTH RP (remove nothere from the email address if mailing direct) "dave glynn" wrote in message ... Hi Bob, Many thanks. What value(s) do Isubstitute for "A"? Dave "Bob Phillips" wrote: For i = 3 to 303 Cells(i,"A").Entirerow.copy destination:=Cells(i-2,"A") Next i -- HTH RP (remove nothere from the email address if mailing direct) "dave glynn" wrote in message ... i have data spread across 6 columns and 300 rows. I have written a macro that moves data from row 3 to row 1. I want to repeat the macro so that it performs the move for row 6 to row 4, row 9 to line 7 etc. Do i have to paste and copy the macro or is there a way of writing it only once so that it repeats till the end of the range? |
Hi Dana,
Thanks a lot. I wasn't too clear in my original note. What I need to do is to move the values in e3 and f3 to e1 and f1, e6 anf f6 to e4 and f4 and so on whilst retaining the rest of the original data in its original cells. Dave "Dana DeLouis" wrote: Another option for the Entire Row might be: Dim R As Long 'Row For R = 3 To 303 Rows(R).Copy Rows(R - 2) Next R performs the move for row 6 to row 4, row 9 to line 7 etc.... Not sure, but if you really meant to copy every third row, consider something like: For R = 6 To 303 Step 3 -- Dana DeLouis Win XP & Office 2003 "Bob Phillips" wrote in message ... Dave, You can leave it as A, that just signifies the column, but as you copy the whole row it doesn't matter. -- HTH RP (remove nothere from the email address if mailing direct) "dave glynn" wrote in message ... Hi Bob, Many thanks. What value(s) do Isubstitute for "A"? Dave "Bob Phillips" wrote: For i = 3 to 303 Cells(i,"A").Entirerow.copy destination:=Cells(i-2,"A") Next i -- HTH RP (remove nothere from the email address if mailing direct) "dave glynn" wrote in message ... i have data spread across 6 columns and 300 rows. I have written a macro that moves data from row 3 to row 1. I want to repeat the macro so that it performs the move for row 6 to row 4, row 9 to line 7 etc. Do i have to paste and copy the macro or is there a way of writing it only once so that it repeats till the end of the range? |
For i = 3 to 303
Cells(i,"E").Resize(1,2).copy destination:=Cells(i-2,"E") Next i -- HTH RP (remove nothere from the email address if mailing direct) "dave glynn" wrote in message ... Hi Dana, Thanks a lot. I wasn't too clear in my original note. What I need to do is to move the values in e3 and f3 to e1 and f1, e6 anf f6 to e4 and f4 and so on whilst retaining the rest of the original data in its original cells. Dave "Dana DeLouis" wrote: Another option for the Entire Row might be: Dim R As Long 'Row For R = 3 To 303 Rows(R).Copy Rows(R - 2) Next R performs the move for row 6 to row 4, row 9 to line 7 etc.... Not sure, but if you really meant to copy every third row, consider something like: For R = 6 To 303 Step 3 -- Dana DeLouis Win XP & Office 2003 "Bob Phillips" wrote in message ... Dave, You can leave it as A, that just signifies the column, but as you copy the whole row it doesn't matter. -- HTH RP (remove nothere from the email address if mailing direct) "dave glynn" wrote in message ... Hi Bob, Many thanks. What value(s) do Isubstitute for "A"? Dave "Bob Phillips" wrote: For i = 3 to 303 Cells(i,"A").Entirerow.copy destination:=Cells(i-2,"A") Next i -- HTH RP (remove nothere from the email address if mailing direct) "dave glynn" wrote in message ... i have data spread across 6 columns and 300 rows. I have written a macro that moves data from row 3 to row 1. I want to repeat the macro so that it performs the move for row 6 to row 4, row 9 to line 7 etc. Do i have to paste and copy the macro or is there a way of writing it only once so that it repeats till the end of the range? |
Thanks Bob, works perfetly!!!
"Bob Phillips" wrote: For i = 3 to 303 Cells(i,"E").Resize(1,2).copy destination:=Cells(i-2,"E") Next i -- HTH RP (remove nothere from the email address if mailing direct) "dave glynn" wrote in message ... Hi Dana, Thanks a lot. I wasn't too clear in my original note. What I need to do is to move the values in e3 and f3 to e1 and f1, e6 anf f6 to e4 and f4 and so on whilst retaining the rest of the original data in its original cells. Dave "Dana DeLouis" wrote: Another option for the Entire Row might be: Dim R As Long 'Row For R = 3 To 303 Rows(R).Copy Rows(R - 2) Next R performs the move for row 6 to row 4, row 9 to line 7 etc.... Not sure, but if you really meant to copy every third row, consider something like: For R = 6 To 303 Step 3 -- Dana DeLouis Win XP & Office 2003 "Bob Phillips" wrote in message ... Dave, You can leave it as A, that just signifies the column, but as you copy the whole row it doesn't matter. -- HTH RP (remove nothere from the email address if mailing direct) "dave glynn" wrote in message ... Hi Bob, Many thanks. What value(s) do Isubstitute for "A"? Dave "Bob Phillips" wrote: For i = 3 to 303 Cells(i,"A").Entirerow.copy destination:=Cells(i-2,"A") Next i -- HTH RP (remove nothere from the email address if mailing direct) "dave glynn" wrote in message ... i have data spread across 6 columns and 300 rows. I have written a macro that moves data from row 3 to row 1. I want to repeat the macro so that it performs the move for row 6 to row 4, row 9 to line 7 etc. Do i have to paste and copy the macro or is there a way of writing it only once so that it repeats till the end of the range? |
Glad we got there.
Regards Bob "dave glynn" wrote in message ... Thanks Bob, works perfetly!!! "Bob Phillips" wrote: For i = 3 to 303 Cells(i,"E").Resize(1,2).copy destination:=Cells(i-2,"E") Next i -- HTH RP (remove nothere from the email address if mailing direct) "dave glynn" wrote in message ... Hi Dana, Thanks a lot. I wasn't too clear in my original note. What I need to do is to move the values in e3 and f3 to e1 and f1, e6 anf f6 to e4 and f4 and so on whilst retaining the rest of the original data in its original cells. Dave "Dana DeLouis" wrote: Another option for the Entire Row might be: Dim R As Long 'Row For R = 3 To 303 Rows(R).Copy Rows(R - 2) Next R performs the move for row 6 to row 4, row 9 to line 7 etc.... Not sure, but if you really meant to copy every third row, consider something like: For R = 6 To 303 Step 3 -- Dana DeLouis Win XP & Office 2003 "Bob Phillips" wrote in message ... Dave, You can leave it as A, that just signifies the column, but as you copy the whole row it doesn't matter. -- HTH RP (remove nothere from the email address if mailing direct) "dave glynn" wrote in message ... Hi Bob, Many thanks. What value(s) do Isubstitute for "A"? Dave "Bob Phillips" wrote: For i = 3 to 303 Cells(i,"A").Entirerow.copy destination:=Cells(i-2,"A") Next i -- HTH RP (remove nothere from the email address if mailing direct) "dave glynn" wrote in message ... i have data spread across 6 columns and 300 rows. I have written a macro that moves data from row 3 to row 1. I want to repeat the macro so that it performs the move for row 6 to row 4, row 9 to line 7 etc. Do i have to paste and copy the macro or is there a way of writing it only once so that it repeats till the end of the range? |
All times are GMT +1. The time now is 06:05 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com