For-Next Loops

For-Next Loops


The simplest type of a good loop is a For-Next loop. Its syntax is:

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
For counter = start To end [Step stepval]
[instructions]
[Exit For]
[instructions]
Next [counter]
[/sourcecode]


Example 1

In this example, Count (the loop counter variable) starts out as 1 and increases by 1 each time the loop repeats. The Sum variable simply accumulates the square roots of each value of Count.

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Sub SumSquareRoots()
Dim Sum As Double
Dim Count As Integer
Sum = 0
For Count = 1 To 100
Sum = Sum + Sqr(Count)
Next Count
MsgBox Sum
End Sub
[/sourcecode]


Example 2

You can also use a Step value to skip some values in the loop. Here’s the same procedure
rewritten to sum the square roots of the odd numbers between 1 and 100:

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Sub SumOddSquareRoots()
Dim Sum As Double
Dim Count As Integer
Sum = 0
For Count = 1 To 100 Step 2
Sum = Sum + Sqr(Count)
Next Count
MsgBox Sum
End Sub
[/sourcecode]


Example 3

Exits the sub when value is found

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Sub ExitForDemo()
Dim MaxVal As Double
Dim Row As Long
For Row = 1 To 1048576
If Cells(Row, 1).Value = MaxVal Then
Exit For
End If
Next Row
MsgBox “Max value is in Row “ & Row
Cells(Row, 1).Activate
End Sub
[/sourcecode]


NOTE:

The ExitForDemo procedure is presented to demonstrate how to exit from a For-Next loop. However, it is not the most efficient way to activate the largest value in a range. In fact, a single statement does the job:

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Range(“A:A”).Find(Application.WorksheetFunction.Max _
(Range(“A:A”))).Activate
[/sourcecode]