Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > How do I write a looping in VBA in Excel?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 16
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,311
Default 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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 16
Default 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 With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to write an array to a range without looping Andy Excel Worksheet Functions 3 April 27th 11 12:52 PM
Looping Through Every Instance of Excel [email protected] Excel Programming 5 March 7th 07 08:49 PM
looping in vba for excel [email protected] Excel Programming 3 October 8th 05 03:05 PM
Looping through Excel Applications big t Excel Programming 2 September 22nd 05 02:21 PM
Excel VBA-Looping through Multiselection jpendegraft Excel Programming 1 February 3rd 04 02:27 PM


All times are GMT +1. The time now is 07:53 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement