Excel: Values Returned in FOR.CELL Loop Incorrect

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

Additional query words:

Keywords :
Version :
Platform :
Issue type :