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

ID: Q79136

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("CurrentCell")
   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("CurrentCell")
   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("Current")
   A2: =SELECT(Current)
   A3: =FORMAT.FONT(2)
   A4: =NEXT()
   A5: =RETURN()

REFERENCES

"Microsoft Excel Functions Reference," version 3.0, page 80

Additional query words: 5.00 3.00 docerr command format font for cell 4.0 4.00

Keywords :
Version :
Platform :
Issue type :