| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
how to properly loop a macro
|
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
how to properly loop a macro
Yes, that did it!!
Thank you thank you thank you!!! --Stephen |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
how to properly loop a macro
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro not working properly when run automatically | Excel Discussion (Misc queries) | |||
| Macro is not working properly | Excel Discussion (Misc queries) | |||
| For Each Loop not working properly | Excel Programming | |||
| Macro Not Working Properly | Excel Programming | |||
| Macro won't run properly | Excel Programming | |||
Linear Mode
