METBD 050For…Next LoopsThe For…Next loop structure provides the VBA programmer with a tool to execute one or more statements a specific number of times. The structure of this useful loop is:For variable = start To end Step incrementOne or more statementsNext variableWhere: variable = The counter variable that is incremented by the step increment at the end of eachpass through the loop. When variable exceeds the end value, looping stops. The variable can be any numeric variable, but a procedure will run faster if the variable is an integer. start = The value of variable for the first pass throught the loop.end = The largest value of variable. If this value is exceeded, looping stops.increment = The amount by which variable is incremented at the end of each pass through theloop. May be positive or negative. The increment is one by default.The body of a For…Next loop doesn’t have to be indented, but it certainly improves the readability of the code. The variable should not be altered within the loop because this may cause unpredictable results. Let the Next statement increment the variable. Beware of using noninteger step values which could cause roundoff errors which may lead to the loop not executing the required number of times.EXAMPLE: Add the first 100 integers and place the result in cell B2.In this case, we have to do something a certain number of times. Specifically, add consecutive numbers 100 times. This is ideal for using the For…Next loop.Sub Adding100()Dim Counter, x As Integer ‘Declare variables Counter = 0 ‘Set Initial Value to 0 For x = 1 To 100 Counter = Counter + x Next x ‘add one & go to For…Range("B2").Value = Counter ‘place results in B2End SubThe following variations of the For statement would have worked as well:For x = 1 To 100 Step 1 For x = 100 To 1 Step -1The loop will run at least one time if the starting value is less than the end value. The loop will not execute atall if start is greater than end and increment is positive. It will also not run if start is less than end and increment is negative.EXAMPLE: Add the even integers from 2 to 100 and place the result in cell A1.Sub AddingEvensTo100()Dim Counter, x As IntegerCounter = 0For x = 2 To 100 Step 2 Counter = Counter + xNext xRange("a1").Value = CounterEnd SubHO-050-VBA-ForNextLoops.doc Page 1 of 33/15/02EXAMPLE: Create a list of numbers from 1 to 10 in range A1:A10 and place their sum at the bottom of the list.Sub ListNumbers ()Dim I As IntegerRange(“A1”).SelectFor I = 1 to 10ActiveCell.Value = IActiveCell.Offset(1, 0).SelectNext IWith ActiveCell.Borders(xlEdgeTop).Weight = xlThick.ColorIndex = 1.LineStyle = xlDoubleEnd WithActiveCell.Formula = “=Sum(A1:A10)”ActiveCell.Offset(0, 1).Value = “ = Sum of Numbers”End SubEXAMPLE: Write a VBA sub procedure to show the ColorIndex colors in the range A1:A56.Sub ShowColors()Dim x As Integer Range("A1").Select For x = 1 To 56 With ActiveCell .Interior.ColorIndex = x .Offset(1, 0).Select End With Next xEnd SubA general flow chart for the For…Next loop is shown in the figure below.HO-050-VBA-ForNextLoops.doc Page 2 of 33/15/02Set Variable = StartIsVariable >End?NoYesExecute Statements Within the LoopIncrement Variable by Step AmountExecute Statements Following the LoopEnter LoopIt is possible to perform a For…Next loop multiple times by nesting a loop within a loop. Also notice that the counter variable can also be used for calculations within the procedure and the loop. In the code, there has to be a one (and only one) Next statement for each For statement.EXAMPLE: Create a table of values for X, 2X, 3X, 4X, 5X, and 6X where X equals 1 to 10.Sub FillCells() For x = 1 To 6 For i = 1 To 10 Cells(i, x).Value = x * i Next i Next xEnd SubNotice that the counter variables for the loops are used to specify the cell, by row and column, to paste the value. The For…Next loop can also be used to access the various elements of a collection object. EXAMPLE: Change the name of all the worksheets in the current workbook to have the form “MySheet#”. In this form, the # should be a unique number.Sub ChangeSheetName() n = Worksheets.Count For i = 1 To n Worksheets(i).Name = "MySheet" & i Next iEnd SubThe For Each block is a variation of the For…Next loop that is designed to work with collections and arrays. This is a powerful tool when working with collection objects and globals.EXAMPLE: Change the name of all the worksheets in the current workbook to have the form “ASheet#”. In this form, the # should be a unique number.Sub ChangeSheetName1() n = 1 For Each Sheet In Worksheets Worksheets(n).Name = "ASheet" & n n = n + 1 Next SheetEnd SubHO-050-VBA-ForNextLoops.doc Page 3 of 33/15/02For iNext iFor jFor kNext kNext jFor iNext iFor jFor kNext kNext jNested