Excel: Values Returned in FOR.CELL Loop Incorrect

Last reviewed: November 29, 1994
Article ID: Q67468

The information in this article applies to:

Microsoft Excel for the Macintosh, versions 3.0, 4.0

SUMMARY

When using a FOR.CELL loop in Microsoft Excel version 3.00 or 4.00, be certain that the modifying statements (such as FORMULA) associated with it are constructed correctly. The first argument of the FOR.CELL function allows you to specify a name that can be used to track the progress of a FOR.CELL loop. It is a common error to use ACTIVE.CELL instead of this specified name.

MORE INFORMATION

The FOR.CELL loop allows Microsoft Excel to work on a range of cells without actually moving the cursor. This greatly enhances macro speed. However, if functions, such as the FORMULA statement, rely on values from the active cell, it is important that the name given in the first argument of the FOR.CELL function is used, rather than the function ACTIVE.CELL.

The following is a correct example of a FOR.CELL loop that goes to each nonblank cell in a selection and adds 23:

   =FOR.CELL("CurrentCell",,TRUE)
   =FORMULA(CurrentCell+23,CurrentCell)
   =NEXT()
   =RETURN()

With a typical FOR statement using SELECT to move from cell to cell, the FORMULA statement will resemble the following:

   =FORMULA(ACTIVE.CELL()+23)

However, in the case of the FOR.CELL loop, the active cell never changes (because the cursor never moves); therefore, using the name defined in the first argument of FOR.CELL (CurrentCell in this example) is the equivalent.

REFERENCES

"Microsoft Excel Function Reference," version 4.0, pages 155-156

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

KBCategory: kbusage
KBSubcategory:
Additional reference words: 3.0 3.00 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 29, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.