Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I write a looping in VBA in Excel?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I write a looping in VBA in Excel?
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 "sebastico" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I write a looping in VBA in Excel?
"PCLIVE" wrote: 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 "sebastico" wrote in message ... 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 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. Thank you very much indeed |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to write an array to a range without looping | Excel Worksheet Functions | |||
Looping Through Every Instance of Excel | Excel Programming | |||
looping in vba for excel | Excel Programming | |||
Looping through Excel Applications | Excel Programming | |||
Excel VBA-Looping through Multiselection | Excel Programming |