how to properly loop a macro

[brawny_javo]

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]

Not exactly clear what you want but

for i = 1 to 200 step 20
cells(i,"d") 'do your thing
next i

 

[brawny_javo]

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]

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]

Yes, that did it!!

Thank you thank you thank you!!!

--Stephen

 

[Don Guillett]

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