how to properly loop a macro

brawny_javo · 05-18-2006, 11:45 AM

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 · 05-18-2006, 11:55 AM

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
dguillett1@austin.rr.com
"brawny_javo" <brawny_javo@yahoo.com> wrote in message
news:1147966814.783726.65730@y43g2000cwc.googlegroups.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 · 05-18-2006, 12:15 PM

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 · 05-18-2006, 12:15 PM

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 · 05-18-2006, 12:45 PM

Yes, that did it!!

Thank you thank you thank you!!!

--Stephen

Don Guillett · 05-18-2006, 01:30 PM

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
dguillett1@austin.rr.com
"witek" <witek7205@spam.gazeta.pl.invalid> wrote in message
news:e4i6a9$3de$1@inews.gazeta.pl...
>
> 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
>>