Microsoft Office Tutorials and References
In Depth Information
Controlling Code Execution
Sub ExitForDemo()
Dim MaxVal As Double
Dim Row As Long
MaxVal = Application.WorksheetFunction.Max(Range(“A:A”))
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
The maximum value in the column is calculated by using the Excel MAX function, and the value is
assigned to the MaxVal variable. The For-Next loop checks each cell in the column. If the cell
being checked is equal to MaxVal , the Exit For statement terminates the loop and the
statements following the Next statement are executed. These statements display the row of the
maximum value and activate the cell.
The ExitForDemo procedure is presented to demonstrate how to exit from a
ForNext loop. However, it’s not the most efficient way to activate the largest value in a
range. In fact, a single statement does the job:
Range(“A:A”).Find(Application.WorksheetFunction.Max _
(Range(“A:A”))).Activate
The previous examples use relatively simple loops. But you can have any number of statements
in the loop, and you can even nest For-Next loops inside other For-Next loops. Here’s an
example that uses nested For-Next loops to initialize a 10 x 10 x 10 array with the value –1 .
When the procedure is finished, each of the 1,000 elements in MyArray contains –1 .
Sub NestedLoops()
Dim MyArray(1 to 10, 1 to 10, 1 to 10)
Dim i As Integer, j As Integer, k As Integer
For i = 1 To 10
For j = 1 To 10
For k = 1 To 10
MyArray(i, j, k) = -1
Next k
Next j
Next i
’ [More code goes here]
End Sub
 
Search JabSto ::




Custom Search