How do I write a looping in VBA in Excel?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to write a looping structure from the following code
Any help is really appreciated
Macro3 Macro
' Macro recorded 07/03/2007 by Sebastico
Rows("3:6").Select
Selection.Insert Shift:=xlDown
Range("A2").Select
Selection.Copy
Range("A3:A6").Select
ActiveSheet.Paste
Rows("8:11").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
Range("A7").Select
Selection.Copy
Range("A8:A11").Select
ActiveSheet.Paste
Rows("13:16").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
Range("A12").Select
Selection.Copy
Range("A13").Select
ActiveSheet.Paste
Range("A13:A16").Select
ActiveSheet.Paste
Rows("18:21").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
Range("A17").Select
Selection.Copy
Range("A18").Select
ActiveSheet.Paste
Range("A18:A21").Select
ActiveSheet.Paste
Range("B2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "1"
Range("C2").Select
ActiveCell.FormulaR1C1 = "aa"
Range("B3").Select
ActiveCell.FormulaR1C1 = "2"
Range("C3").Select
ActiveCell.FormulaR1C1 = "bb"
Range("B4").Select
ActiveCell.FormulaR1C1 = "3"
Range("C4").Select
ActiveCell.FormulaR1C1 = "cc"
Range("B5").Select
ActiveCell.FormulaR1C1 = "4"
Range("C5").Select
ActiveCell.FormulaR1C1 = "dd"
Range("B6").Select
ActiveCell.FormulaR1C1 = "5"
Range("C6").Select
ActiveCell.FormulaR1C1 = "ee"
Range("B2:C6").Select
Selection.Copy
Range("B7:C21").Select
ActiveSheet.Paste
 
It's hard to speculate what you're trying to accomplish with this. Though
I'm sure there are other, possibly better, ways to do this, you could add
some looping to your code using the code below.

n = 3
n2 = 6

For i = 1 To 4
Rows(n & ":" & n2).Select
Selection.Insert Shift:=xlDown
Range("A" & n - 1).Select
Selection.Copy
Range("A" & n & ":A" & n2).Select
ActiveSheet.Paste
n = n + 5
n2 = n + 3
Next i


Range("B2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "1"
Range("C2").Select
ActiveCell.FormulaR1C1 = "aa"
Range("B3").Select
ActiveCell.FormulaR1C1 = "2"
Range("C3").Select
ActiveCell.FormulaR1C1 = "bb"
Range("B4").Select
ActiveCell.FormulaR1C1 = "3"
Range("C4").Select
ActiveCell.FormulaR1C1 = "cc"
Range("B5").Select
ActiveCell.FormulaR1C1 = "4"
Range("C5").Select
ActiveCell.FormulaR1C1 = "dd"
Range("B6").Select
ActiveCell.FormulaR1C1 = "5"
Range("C6").Select
ActiveCell.FormulaR1C1 = "ee"
Range("B2:C6").Select
Selection.Copy
Range("B7:C21").Select
ActiveSheet.Paste
 
PCLIVE said:
It's hard to speculate what you're trying to accomplish with this. Though
I'm sure there are other, possibly better, ways to do this, you could add
some looping to your code using the code below.

n = 3
n2 = 6

For i = 1 To 4
Rows(n & ":" & n2).Select
Selection.Insert Shift:=xlDown
Range("A" & n - 1).Select
Selection.Copy
Range("A" & n & ":A" & n2).Select
ActiveSheet.Paste
n = n + 5
n2 = n + 3
Next i


Range("B2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "1"
Range("C2").Select
ActiveCell.FormulaR1C1 = "aa"
Range("B3").Select
ActiveCell.FormulaR1C1 = "2"
Range("C3").Select
ActiveCell.FormulaR1C1 = "bb"
Range("B4").Select
ActiveCell.FormulaR1C1 = "3"
Range("C4").Select
ActiveCell.FormulaR1C1 = "cc"
Range("B5").Select
ActiveCell.FormulaR1C1 = "4"
Range("C5").Select
ActiveCell.FormulaR1C1 = "dd"
Range("B6").Select
ActiveCell.FormulaR1C1 = "5"
Range("C6").Select
ActiveCell.FormulaR1C1 = "ee"
Range("B2:C6").Select
Selection.Copy
Range("B7:C21").Select
ActiveSheet.Paste




PCLIVE
The code works excellent for the loop. For the second part of my question I just added to the end of your code and works.
Click to expand...
Thank you very much indeed
 
Back
Top