Microsoft Office Tutorials and References
In Depth Information
For…Next
for…next
The For…Next loop structure is a simple and effective way to repeat an action for a specified number
of times. For example, if you want to add five new worksheets to your workbook, you could declare
an Integer type variable and repeat the action five times, like this:
Sub AddFiveWorksheets()
‘Declare your Integer or Long variable.
Dim intCounter As Integer
‘Open the For loop structure.
For intCounter = 1 To 5
‘Enter the command(s)that will be repeated.
Worksheets.Add
‘Loop to the next iteration.
Next intCounter
End Sub
Although it is technically correct that the Next statement can stand alone, do
yourself a favor by getting into the good habit of including the variable in the
Next statement. For example, writing your code as Next intCounter instead of
just as Next will make it easier for you to read.
When VBA executes a For…Next loop, by default it increments by 1 the value of the declared
Integer or Long type variable. Because the objective was to add five worksheets, the easiest way
to keep a running count of the process is to iterate five times, just as if you were counting the
occurrence of each action from 1 to 5.
You can take advantage of the fixed nature of a For…Next loop by asking for the number of
worksheets that are to be added. In the following example, an InputBox engages the user by asking for
a number that represents how many worksheets will be added:
Sub ForNextExample2()
‘Declare your Integer or Long variables.
Dim MoreSheets As Integer, intCounter As Integer
‘Define the MoreSheets variable with an InputBox.
MoreSheets = InputBox( _
“How many worksheets do you want to add?”, _
“Enter a number”)
‘Open the For loop structure.
For intCounter = 1 To MoreSheets
‘Enter the command(s)that will be repeated.
Worksheets.Add
‘Loop to the next iteration.
Next intCounter
End Sub
 
Search JabSto ::




Custom Search