Page 49 - Excel for Scientists and Engineers: Numerical Methods

26 EXCEL: NUMERICAL METHODS

language. The For Each ... Next loop executes the statements within the loop for
each object in a group of objects. Figure 2-8 illustrates the syntax of the
statement.

For Each Element In Group
statements
Next Element
Figure 2-8. The VBA For Each ... Next structure.

The For Each ... Next loop returns an object variable in each pass through the
loop. You can access or use all of the properties or methods that apply to
Element. For example, in a loop such as the one shown in Figure 2-9, the
variable cel is an object that has all the properties of a cell (a Range object):
Value, Formula, NumberFormat, etc.

For Each cel In Selection
FormulaText = cel.Value
statements
Next cel
Figure 2-9. Example of a For Each ... Next loop.

Note that there is no integer loop counter, as in the For Counter = Start To
End type of loop structure. If an integer counter is needed, you will have to
initialize one outside the loop, and increment it inside the loop.

Nested Loops
Often one loop must be nested inside another, as illustrated in the following
example.

ForI=l TONI
statements
For J = 1 To N2
statements
Next J
Next I

Figure 2-10. Example of nested loops.

Exiting from a Loop or from a Procedure

Often you use a loop structure to search through an array or collection of
objects, looking for a certain value or property. Once you find a match, you
don't need to cycle through the rest of the loops. You can exit from the loop