Microsoft KB Archive/67468

{|
 * width="100%"|

Excel: Values Returned in FOR.CELL Loop Incorrect

 * }

-

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(&quot;CurrentCell&quot;,,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.