Microsoft KB Archive/79136

{|
 * width="100%"|

Excel: Operation Performs on One Cell Only in a FOR.CELL Loop

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, version 3.0, 4.0, 4.0a, 5.0
 * Microsoft Excel for OS/2, version 3.0

-

SUMMARY
A FOR.CELL loop repeats instructions over a range of cells, one cell at a time. However, the active cell does not move during the loop's execution. This may cause problems if you have instructions within the body of the loop that operate on the active cell.

MORE INFORMATION
The first argument to the FOR.CELL command is ref_name. This is a name in the form of text that refers to the n-th cell in the selection upon the n-th pass through the loop. The example shown below will replace the contents of the active cell with the cubed root of the current value of the active cell:

  A1: CubeRoot A2: =FOR.CELL(&quot;CurrentCell&quot;) A3: =FORMULA(CurrentCell^(1/3)) A4: =NEXT A5: =RETURN

Because the reference argument is omitted from the FORMULA function, it defaults to the active cell. However, because the active cell is not moved in a FOR.CELL loop, the only cell that gets operated on is the cell that is active before the loop is executed.

To cause a FOR.CELL loop's operation to be applied to each cell in the selection, you must either use a SELECT statement to change the active cell, or specify a reference if the operation to be applied will accept a reference as an argument.

Example
In the example shown below, the FORMULA function is given the name CurrentCell as its reference argument. This works properly.

  A1: CubeRoot A2: =FOR.CELL(&quot;CurrentCell&quot;) A3: =FORMULA(CurrentCell^(1/3),CurrentCell) A4: =NEXT A5: =RETURN

If a command such as FORMAT.FONT is used within the body of the loop, you must first execute a SELECT statement because FORMAT.FONT doesn't accept a reference as an argument. For example:

  A1: =FOR.CELL(&quot;Current&quot;) A2: =SELECT(Current) A3: =FORMAT.FONT(2) A4: =NEXT A5: =RETURN