Using For Each...Next Statements
For Each...Next statements repeat a block of statements for each object in a collection or each element in an array. Visual Basic automatically sets a variable each time the loop runs. For example, the following procedure closes all forms except the form containing the procedure that’s running.
The following code loops through each element in an array and sets the value of each to the value of the index variable I.
Looping Through a Range of Cells
Use a For Each...Next loop to loop through the cells in a range. The following procedure loops through the range A1:D10 on Sheet1 and sets any number whose absolute value is less than 0.01 to 0 (zero).
Exiting a For Each...Next Loop Before it is Finished
You can exit a For Each...Next loop using the Exit For statement. For example, when an error occurs, use the Exit For statement in the True statement block of either an If...Then...Else statement or a Select Case statement that specifically checks for the error. If the error does not occur, then the If…Then…Else statement is False and the loop continues to run as expected.
The following example tests for the first cell in the range A1:B5 that does not contain a number. If such a cell is found, a message is displayed and Exit For exits the loop.