To explain what's happening:
If you create a circular reference ( for example by putting the formulae
=IF(D10=0,NOW(),0) in cell D10) then you need to tell Excel to solve for
circular references by ticking the Iteration checkbox (Tools--Options) and
setting Iterations to an appropriate number (like 1 to single-step the
iterations, so that every time you press F9 the cell flipflops between 0 and
the current time).
If you don't tick the Iteration checkbox then the cell will not recalculate
(because Excel knows it is circular) unless you re-enter the formula, which
resets the cell to zero and then forces Excel to re-evaluate the cell, thus
giving you the time you re-entered the formula.
The standard warning on circular references applies: using a deliberate
circular reference hides unintentional circular refs.
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com
"Michael.Tarnowski" wrote in message
...
On Feb 11, 11:27 am, "Michael.Tarnowski" wrote:
In a post in ExcelForum
(http://www.excelforum.com/excel-prog...6-application-
calculate-does-not-always-update-recalculate-the-for.html)
I found following information concering Recalculation:
Present documentation shows the following:
* F9 - recalculates all of the data in the open workbooks
(Application.Calculate)
* Shift+F9 - only calculates data in the specified worksheet
(ActiveSheet.Calculate)
* Ctrl+Alt+F9 - Forces a full calculation of the data in all of the
open
workbooks (Application.CalculateFull)
* Ctrl+Shift+Alt+F9 - Forces a full calculation of the data in all of
the
open workbooks after checking the dependencies between formulas
(Application.CalculateFullRebuild)
In an application where circular references are use - like =IF
(D10=0;NOW();D10)) - I discovered the following behaviour: none of the
mentioned above techniques changed the cell (D10), only placing the
cursor in the formular and hitting <Return afterwards (="Pseudo
Editing") changed the content of D10.
Any ideas how to achieve this with VBA?
Michael
Problem solved!!
In ExcelForum again
http://www.excelforum.com/excel-prog...ithout-f2.html
I found a cheat to solve this: rebuilding the formula in question:
Sub RedoFormulae()
Range("HiddenTotal").FormulaR1C1 = "=sumhiddenrows(NumberRange)"
Range("VisibleTotal").FormulaR1C1 = "=sumvisiblerows(NumberRange)"
End Sub
That rocks! - Thus I have only to cycle through my cells in question
to rebuild the formula
Have a nice day
Michael