A For-Next loop can also include one or more Exit For statements within the loop. When VBA encounters this statement, the loop terminates immediately. The following example, available on the book’s Web site, demonstrates the Exit For statement. This routine identifies which of the active worksheet’s cells in column A has the largest value:
Sub ExitForDemo()
Dim MaxVal As Double
Dim Row As Long
MaxVal = Application.WorksheetFunction. _
Max(Range(“A:A”))
For Row = 1 To Rows.Count
If Range(“A1”).Offset(Row – 1, 0).Value = MaxVal
Then
Range(“A1”).Offset(Row – 1, 0).Activate
MsgBox “Max value is in Row “ & Row
Exit For
End If
Next Row
End Sub
The routine calculates the maximum value in the column by using Excel’s MAX function and assigns the result to the MaxVal variable. The For-Next loop then checks each cell in the column. If the cell being checked is equal to MaxVal, the routine doesn’t need to continue looping (its job is finished), so the Exit For statement terminates the loop. Before terminating the loop, the procedure activates the cell with the maximum value and informs the user of its location. Notice that I use Rows.Count in the For statement. The count property of the Rows objects returns the number of rows in the worksheet. Therefore, you can use this procedure with Excel 2007 as well as with earlier versions (which have fewer rows).
