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

 Last reviewed: November 30, 1994
Article ID: Q79136
The information in this article applies to
  • Microsoft Excel for Windows, versions 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

KBCategory: kbusage
KBSubcategory:

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

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 30, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.