Cells won't recalculate in manual mode with F9.

Is there a recalculation problem in automatic mode?

Tom wrote:

Some cells won't manually recalculate.  Have large/complex file set to manual calc.  After hitting F9, some cells (with very simple formulas) don't recalculate.  If I edit the cell and hit return, then that individual cell calcs.  How to fix?

What makes you think the cell should recalculate when F9 is pressed?  More to the point, what have you (not) told us to make us think it should be?

Note that when you press F9, Excel will only "[r]ecalculate formulas that have changed since the last calculation, and formulas dependent on them".

Without seeing the formula, we can only make wild-a.s.s guesses.  My first WAG....

Perhaps your formula includes a reference to a user-defined [removed_js] that you intend to be called each time Excel decides to recalcuate.  If that is the case, you need to do one of several things.


1. Make the UDF volatile by including Application.Volatile in the main code path, usually as the first executable statement.

Personally, I do not like this solution.  It causes all formulas (and their dependents) calling the UDF to be recalculated every time any cell in any worksheet in the workbook is edited.


2. Pass a cell or range reference to the UDF.  Thus, formulas (and their dependents) that call the UDF are recalculated whenever any cell in the passed reference is recalculated.

In order for this to work, the UDF must actually access the passed references.  Also, I believe (not sure) that the UDF must access the value of the passed references.  It is not sufficient to access other attributes of the passed references, e.g. their color.


3. Create a "control" (button) associated with a macro that executes Range("a1").Dirty, then Range("a1").Calculate, for example.  (Suppose A1 is the cell you want to force to be recalculated.)

This gives you the greatest control over when the desired cell or cells are recalculated.

To create a "control", click on Developer, then Insert (Controls), select the desired control and edit the macro.  If you do not see Developer tab above the ribbon, click on File, Options, Customize Ribbon, and follow the necessary steps.

No UDFs used.  Only data poiints changed.  I changed data on sheet 1.  Formulas on sheet 2 that refer to sheet 1 do not recalculate after hitting F9.  Putting the cursor over the cell in Sheet2 that contains the formula referring to sheet 1 and hitting enter WILL cause that one cell to recalculate for the correct answer.  Lots and lots of cells in sheet 2 with formulas that refer to sheet 1 do not calculate.  Does that help?

Is there a recalculation problem in automatic mode?

Don't know.  Why would that make a difference since I can never use Auto mode for this file (takes too long to recalculate after ever new data entry)?

Not to other workbooks, but to other sheets in the same workbook.  Yes, it has happened on more than one file.

Tom wrote:

No UDFs used.  Only data poiints changed.  I changed data on sheet 1.  Formulas on sheet 2 that refer to sheet 1 do not recalculate after hitting F9.  Putting the cursor over the cell in Sheet2 that contains the formula referring to sheet 1 and hitting enter WILL cause that one cell to recalculate for the correct answer.  Lots and lots of cells in sheet 2 with formulas that refer to sheet 1 do not calculate.  Does that help?

Well, it does rule out one WAG.  But at the risk of repeating the obvious....

"Without seeing the formula, we can only make wild-a.s.s guesses."

[EDIT] And of course you need to say which cells referenced by that formula changed and how.  (The "change" must be in content, not attributes.)

I tried the above and suggestions in many other forums to no avail. I found I had circular references that were causing the problem. I fixed those by correctly assigning the cells and my problems were solved. I hope this helps.