In programming terms, iteration involves the repetition of a series of programming instructions. Iterations are also commonly referred to as loops. If you want to perform an action on, or test each cell in a range, it is useful to be able to iterate over the range, (perform the same series of instructions for each cell in the range).

 

To do this, you must first declare a variable that can assume the contents of a cell.

 

Dim tempCell as Range

 

This statement declares a variable called tempCell as type Range.

 

Now look at the following VBA statements.

 

For Each tempCell In Range (“A1:A10”)

tempCell = tempCell^2

Next tempCell

 

The first statement says that the cells in range A1:A10 are going to be accessed one at a time, and that the value of the cell currently being accessed will be represented by the variable tempCell.

 

The next line raises the current value of tempCell to the second power, and assigns this new value back to the tempCell variable. Because the tempCell variable represents the value of the current cell, the value in the current cell will be the same as the value tempCell (the value in the cell will be squared).

 

The last line steps the iteration forward to the next cell, and execution of the statements repeats, except that now, the current cell has advanced by one. This means that tempCell will now represent the next cell (now the current cell) in the range.

 

The entire range will be iterated (stepped through cell by cell), with the calculation in the second statement being repeated at each iteration (for each cell). This will square the value of every cell in the range.

 

The following image shows an Excel macro that will iterate over the range A1:A10 and square each cell value.

 

 

 

 

 

 

The image on the top shows the range before the macro has been executed and the image on the bottom shows the range after the macro has been executed. Each cell value in the range has been squared.