ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   how to properly loop a macro (https://www.excelbanter.com/excel-programming/361822-how-properly-loop-macro.html)

brawny_javo
how to properly loop a macro
 
I am fixing a badly made spreadsheet, and I am a bit of an Excel macro
novice. I have recorded the following macro to move data into useable
areas, but I need to be able to have the macro restart 20 lines down
(and perform each function 20 lines down), and then restart every 20
lines down from that, etc etc all the way down to line 8100. Any help
would be greatly appreciated!!

Sub Macro3()
Range("A1").Select
Selection.Cut
Range("A2").Select
ActiveSheet.Paste
Range("B2").Select
Selection.Cut
Range("B1").Select
ActiveSheet.Paste
Range("B3").Select
Selection.Cut
Range("C1").Select
ActiveSheet.Paste
Range("B4").Select
Selection.Cut
Range("D1").Select
ActiveSheet.Paste
Range("B5").Select
Selection.Cut
Range("E1").Select
ActiveSheet.Paste
Range("B6").Select
Selection.Cut
Range("F1").Select
ActiveSheet.Paste
Range("B7").Select
Selection.Cut
Range("G1").Select
ActiveSheet.Paste
Range("B8").Select
Selection.Cut
Range("H1").Select
ActiveSheet.Paste
Range("B9").Select
Selection.Cut
Range("I1").Select
ActiveSheet.Paste
Range("B10").Select
Selection.Cut
Range("J1").Select
ActiveSheet.Paste
Range("B10").Select
Selection.Cut
Range("J1").Select
ActiveSheet.Paste
Range("B11").Select
Selection.Cut
Range("K1").Select
ActiveSheet.Paste
Range("B12").Select
Selection.Cut
Range("L1").Select
ActiveSheet.Paste
Range("B13").Select
Selection.Cut
Range("M1").Select
ActiveSheet.Paste
Range("B14").Select
Selection.Cut
Range("N1").Select
ActiveSheet.Paste
Range("B15").Select
Selection.Cut
Range("O1").Select
ActiveSheet.Paste
Range("B16").Select
Selection.Cut
Range("P1").Select
ActiveSheet.Paste
Range("B17").Select
Selection.Cut
Range("Q1").Select
ActiveSheet.Paste
Range("B18").Select
Selection.Cut
Range("R1").Select
ActiveSheet.Paste
Range("B19").Select
Selection.Cut
Range("S1").Select
ActiveSheet.Paste
Range("B20").Select
Selection.Cut
Range("T1").Select
ActiveSheet.Paste
Range("C2").Select
Selection.Cut
Range("B2").Select
ActiveSheet.Paste
Range("C3").Select
Selection.Cut
Range("C2").Select
ActiveSheet.Paste
Range("C4").Select
Selection.Cut
Range("D2").Select
ActiveSheet.Paste
Range("C5").Select
Selection.Cut
Range("E2").Select
ActiveSheet.Paste
Range("C6").Select
Selection.Cut
Range("F2").Select
ActiveSheet.Paste
Range("C7").Select
Selection.Cut
Range("G2").Select
ActiveSheet.Paste
Range("C8").Select
Selection.Cut
Range("H2").Select
ActiveSheet.Paste
Range("C9").Select
Selection.Cut
Range("I2").Select
ActiveSheet.Paste
Range("C10").Select
Selection.Cut
Range("J2").Select
ActiveSheet.Paste
Range("C11").Select
Selection.Cut
Range("K2").Select
ActiveSheet.Paste
Range("C12").Select
Selection.Cut
Range("L2").Select
ActiveSheet.Paste
Range("C13").Select
Selection.Cut
Range("M2").Select
ActiveSheet.Paste
Range("C14").Select
Selection.Cut
Range("N2").Select
ActiveSheet.Paste
Range("C15").Select
Selection.Cut
Range("O2").Select
ActiveSheet.Paste
Range("C16").Select
Selection.Cut
Range("P2").Select
ActiveSheet.Paste
Range("C17").Select
Selection.Cut
Range("Q2").Select
ActiveSheet.Paste
Range("C18").Select
Selection.Cut
Range("R2").Select
ActiveSheet.Paste
Range("C19").Select
Selection.Cut
Range("S2").Select
ActiveSheet.Paste
Range("C20").Select
Selection.Cut
Range("T2").Select
ActiveSheet.Paste
End Sub

Thanks,
Stephen

Don Guillett
how to properly loop a macro
 
Not exactly clear what you want but

for i = 1 to 200 step 20
cells(i,"d") 'do your thing
next i
--
Don Guillett
SalesAid Software

"brawny_javo" wrote in message
ups.com...
I am fixing a badly made spreadsheet, and I am a bit of an Excel macro
novice. I have recorded the following macro to move data into useable
areas, but I need to be able to have the macro restart 20 lines down
(and perform each function 20 lines down), and then restart every 20
lines down from that, etc etc all the way down to line 8100. Any help
would be greatly appreciated!!

Sub Macro3()
Range("A1").Select
Selection.Cut
Range("A2").Select
ActiveSheet.Paste
Range("B2").Select
Selection.Cut
Range("B1").Select
ActiveSheet.Paste
Range("B3").Select
Selection.Cut
Range("C1").Select
ActiveSheet.Paste
Range("B4").Select
Selection.Cut
Range("D1").Select
ActiveSheet.Paste
Range("B5").Select
Selection.Cut
Range("E1").Select
ActiveSheet.Paste
Range("B6").Select
Selection.Cut
Range("F1").Select
ActiveSheet.Paste
Range("B7").Select
Selection.Cut
Range("G1").Select
ActiveSheet.Paste
Range("B8").Select
Selection.Cut
Range("H1").Select
ActiveSheet.Paste
Range("B9").Select
Selection.Cut
Range("I1").Select
ActiveSheet.Paste
Range("B10").Select
Selection.Cut
Range("J1").Select
ActiveSheet.Paste
Range("B10").Select
Selection.Cut
Range("J1").Select
ActiveSheet.Paste
Range("B11").Select
Selection.Cut
Range("K1").Select
ActiveSheet.Paste
Range("B12").Select
Selection.Cut
Range("L1").Select
ActiveSheet.Paste
Range("B13").Select
Selection.Cut
Range("M1").Select
ActiveSheet.Paste
Range("B14").Select
Selection.Cut
Range("N1").Select
ActiveSheet.Paste
Range("B15").Select
Selection.Cut
Range("O1").Select
ActiveSheet.Paste
Range("B16").Select
Selection.Cut
Range("P1").Select
ActiveSheet.Paste
Range("B17").Select
Selection.Cut
Range("Q1").Select
ActiveSheet.Paste
Range("B18").Select
Selection.Cut
Range("R1").Select
ActiveSheet.Paste
Range("B19").Select
Selection.Cut
Range("S1").Select
ActiveSheet.Paste
Range("B20").Select
Selection.Cut
Range("T1").Select
ActiveSheet.Paste
Range("C2").Select
Selection.Cut
Range("B2").Select
ActiveSheet.Paste
Range("C3").Select
Selection.Cut
Range("C2").Select
ActiveSheet.Paste
Range("C4").Select
Selection.Cut
Range("D2").Select
ActiveSheet.Paste
Range("C5").Select
Selection.Cut
Range("E2").Select
ActiveSheet.Paste
Range("C6").Select
Selection.Cut
Range("F2").Select
ActiveSheet.Paste
Range("C7").Select
Selection.Cut
Range("G2").Select
ActiveSheet.Paste
Range("C8").Select
Selection.Cut
Range("H2").Select
ActiveSheet.Paste
Range("C9").Select
Selection.Cut
Range("I2").Select
ActiveSheet.Paste
Range("C10").Select
Selection.Cut
Range("J2").Select
ActiveSheet.Paste
Range("C11").Select
Selection.Cut
Range("K2").Select
ActiveSheet.Paste
Range("C12").Select
Selection.Cut
Range("L2").Select
ActiveSheet.Paste
Range("C13").Select
Selection.Cut
Range("M2").Select
ActiveSheet.Paste
Range("C14").Select
Selection.Cut
Range("N2").Select
ActiveSheet.Paste
Range("C15").Select
Selection.Cut
Range("O2").Select
ActiveSheet.Paste
Range("C16").Select
Selection.Cut
Range("P2").Select
ActiveSheet.Paste
Range("C17").Select
Selection.Cut
Range("Q2").Select
ActiveSheet.Paste
Range("C18").Select
Selection.Cut
Range("R2").Select
ActiveSheet.Paste
Range("C19").Select
Selection.Cut
Range("S2").Select
ActiveSheet.Paste
Range("C20").Select
Selection.Cut
Range("T2").Select
ActiveSheet.Paste
End Sub

Thanks,
Stephen

brawny_javo
how to properly loop a macro
 
Hmmm...that didn't seem to do it. I want to have my macro run, and
then restart at A21, and all subsequent lines within the macro to also
be 20 lines down (A21 moves to A22, B22 to B21, B23 to C21, etc etc),
and then to restart the whole thing again another 20 lines down
(restarting this time at A41 to A42, B42 to B41, B43 to C41), and
restarting every 20 lines up to line 8100. Possibly I inserted your
solution incorrectly into my macro...
Again, I am a macro novice, so you may have to spell it out for me.

Thanks much!
Stephen

witek
how to properly loop a macro
 

for i= 0 to 53

Range("A1").offset (20*i,0).Select
Selection.Cut
Range("A2").offset(20* i,0).Select
ActiveSheet.Paste
..
..
..

next i


Replace 53 by number of repeats or build your own expression instead of 20*i
Three dots means the rest of your macro with offset added.
Is it clear for you?





brawny_javo wrote:
I am fixing a badly made spreadsheet, and I am a bit of an Excel macro
novice. I have recorded the following macro to move data into useable
areas, but I need to be able to have the macro restart 20 lines down
(and perform each function 20 lines down), and then restart every 20
lines down from that, etc etc all the way down to line 8100. Any help
would be greatly appreciated!!

Sub Macro3()
Range("A1").Select
Selection.Cut
Range("A2").Select
ActiveSheet.Paste
Range("B2").Select
Selection.Cut
Range("B1").Select
ActiveSheet.Paste
Range("B3").Select
Selection.Cut
Range("C1").Select
ActiveSheet.Paste
Range("B4").Select
Selection.Cut
Range("D1").Select
ActiveSheet.Paste
Range("B5").Select
Selection.Cut
Range("E1").Select
ActiveSheet.Paste
Range("B6").Select
Selection.Cut
Range("F1").Select
ActiveSheet.Paste
Range("B7").Select
Selection.Cut
Range("G1").Select
ActiveSheet.Paste
Range("B8").Select
Selection.Cut
Range("H1").Select
ActiveSheet.Paste
Range("B9").Select
Selection.Cut
Range("I1").Select
ActiveSheet.Paste
Range("B10").Select
Selection.Cut
Range("J1").Select
ActiveSheet.Paste
Range("B10").Select
Selection.Cut
Range("J1").Select
ActiveSheet.Paste
Range("B11").Select
Selection.Cut
Range("K1").Select
ActiveSheet.Paste
Range("B12").Select
Selection.Cut
Range("L1").Select
ActiveSheet.Paste
Range("B13").Select
Selection.Cut
Range("M1").Select
ActiveSheet.Paste
Range("B14").Select
Selection.Cut
Range("N1").Select
ActiveSheet.Paste
Range("B15").Select
Selection.Cut
Range("O1").Select
ActiveSheet.Paste
Range("B16").Select
Selection.Cut
Range("P1").Select
ActiveSheet.Paste
Range("B17").Select
Selection.Cut
Range("Q1").Select
ActiveSheet.Paste
Range("B18").Select
Selection.Cut
Range("R1").Select
ActiveSheet.Paste
Range("B19").Select
Selection.Cut
Range("S1").Select
ActiveSheet.Paste
Range("B20").Select
Selection.Cut
Range("T1").Select
ActiveSheet.Paste
Range("C2").Select
Selection.Cut
Range("B2").Select
ActiveSheet.Paste
Range("C3").Select
Selection.Cut
Range("C2").Select
ActiveSheet.Paste
Range("C4").Select
Selection.Cut
Range("D2").Select
ActiveSheet.Paste
Range("C5").Select
Selection.Cut
Range("E2").Select
ActiveSheet.Paste
Range("C6").Select
Selection.Cut
Range("F2").Select
ActiveSheet.Paste
Range("C7").Select
Selection.Cut
Range("G2").Select
ActiveSheet.Paste
Range("C8").Select
Selection.Cut
Range("H2").Select
ActiveSheet.Paste
Range("C9").Select
Selection.Cut
Range("I2").Select
ActiveSheet.Paste
Range("C10").Select
Selection.Cut
Range("J2").Select
ActiveSheet.Paste
Range("C11").Select
Selection.Cut
Range("K2").Select
ActiveSheet.Paste
Range("C12").Select
Selection.Cut
Range("L2").Select
ActiveSheet.Paste
Range("C13").Select
Selection.Cut
Range("M2").Select
ActiveSheet.Paste
Range("C14").Select
Selection.Cut
Range("N2").Select
ActiveSheet.Paste
Range("C15").Select
Selection.Cut
Range("O2").Select
ActiveSheet.Paste
Range("C16").Select
Selection.Cut
Range("P2").Select
ActiveSheet.Paste
Range("C17").Select
Selection.Cut
Range("Q2").Select
ActiveSheet.Paste
Range("C18").Select
Selection.Cut
Range("R2").Select
ActiveSheet.Paste
Range("C19").Select
Selection.Cut
Range("S2").Select
ActiveSheet.Paste
Range("C20").Select
Selection.Cut
Range("T2").Select
ActiveSheet.Paste
End Sub

Thanks,
Stephen

brawny_javo
how to properly loop a macro
 
Yes, that did it!!

Thank you thank you thank you!!!

--Stephen

Don Guillett
how to properly loop a macro
 
If that is the answer and you have values, without selections, try
Range("a2").offset (20*i,0).value= _
Range("A1").offset (20*i,0).value



--
Don Guillett
SalesAid Software

"witek" wrote in message
...

for i= 0 to 53

Range("A1").offset (20*i,0).Select
Selection.Cut
Range("A2").offset(20* i,0).Select
ActiveSheet.Paste
.
.
.

next i


Replace 53 by number of repeats or build your own expression instead of
20*i
Three dots means the rest of your macro with offset added.
Is it clear for you?





brawny_javo wrote:
I am fixing a badly made spreadsheet, and I am a bit of an Excel macro
novice. I have recorded the following macro to move data into useable
areas, but I need to be able to have the macro restart 20 lines down
(and perform each function 20 lines down), and then restart every 20
lines down from that, etc etc all the way down to line 8100. Any help
would be greatly appreciated!!

Sub Macro3()
Range("A1").Select
Selection.Cut
Range("A2").Select
ActiveSheet.Paste
Range("B2").Select
Selection.Cut
Range("B1").Select
ActiveSheet.Paste
Range("B3").Select
Selection.Cut
Range("C1").Select
ActiveSheet.Paste
Range("B4").Select
Selection.Cut
Range("D1").Select
ActiveSheet.Paste
Range("B5").Select
Selection.Cut
Range("E1").Select
ActiveSheet.Paste
Range("B6").Select
Selection.Cut
Range("F1").Select
ActiveSheet.Paste
Range("B7").Select
Selection.Cut
Range("G1").Select
ActiveSheet.Paste
Range("B8").Select
Selection.Cut
Range("H1").Select
ActiveSheet.Paste
Range("B9").Select
Selection.Cut
Range("I1").Select
ActiveSheet.Paste
Range("B10").Select
Selection.Cut
Range("J1").Select
ActiveSheet.Paste
Range("B10").Select
Selection.Cut
Range("J1").Select
ActiveSheet.Paste
Range("B11").Select
Selection.Cut
Range("K1").Select
ActiveSheet.Paste
Range("B12").Select
Selection.Cut
Range("L1").Select
ActiveSheet.Paste
Range("B13").Select
Selection.Cut
Range("M1").Select
ActiveSheet.Paste
Range("B14").Select
Selection.Cut
Range("N1").Select
ActiveSheet.Paste
Range("B15").Select
Selection.Cut
Range("O1").Select
ActiveSheet.Paste
Range("B16").Select
Selection.Cut
Range("P1").Select
ActiveSheet.Paste
Range("B17").Select
Selection.Cut
Range("Q1").Select
ActiveSheet.Paste
Range("B18").Select
Selection.Cut
Range("R1").Select
ActiveSheet.Paste
Range("B19").Select
Selection.Cut
Range("S1").Select
ActiveSheet.Paste
Range("B20").Select
Selection.Cut
Range("T1").Select
ActiveSheet.Paste
Range("C2").Select
Selection.Cut
Range("B2").Select
ActiveSheet.Paste
Range("C3").Select
Selection.Cut
Range("C2").Select
ActiveSheet.Paste
Range("C4").Select
Selection.Cut
Range("D2").Select
ActiveSheet.Paste
Range("C5").Select
Selection.Cut
Range("E2").Select
ActiveSheet.Paste
Range("C6").Select
Selection.Cut
Range("F2").Select
ActiveSheet.Paste
Range("C7").Select
Selection.Cut
Range("G2").Select
ActiveSheet.Paste
Range("C8").Select
Selection.Cut
Range("H2").Select
ActiveSheet.Paste
Range("C9").Select
Selection.Cut
Range("I2").Select
ActiveSheet.Paste
Range("C10").Select
Selection.Cut
Range("J2").Select
ActiveSheet.Paste
Range("C11").Select
Selection.Cut
Range("K2").Select
ActiveSheet.Paste
Range("C12").Select
Selection.Cut
Range("L2").Select
ActiveSheet.Paste
Range("C13").Select
Selection.Cut
Range("M2").Select
ActiveSheet.Paste
Range("C14").Select
Selection.Cut
Range("N2").Select
ActiveSheet.Paste
Range("C15").Select
Selection.Cut
Range("O2").Select
ActiveSheet.Paste
Range("C16").Select
Selection.Cut
Range("P2").Select
ActiveSheet.Paste
Range("C17").Select
Selection.Cut
Range("Q2").Select
ActiveSheet.Paste
Range("C18").Select
Selection.Cut
Range("R2").Select
ActiveSheet.Paste
Range("C19").Select
Selection.Cut
Range("S2").Select
ActiveSheet.Paste
Range("C20").Select
Selection.Cut
Range("T2").Select
ActiveSheet.Paste
End Sub

Thanks,
Stephen


All times are GMT +1. The time now is 01:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com