On Feb 11, 1:25 pm, "Michael.Tarnowski" wrote:
On Feb 11, 12:18 pm, "Charles Williams"
wrote:



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 Sitehttp://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...tions-wont-cal...
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

Charles your right, but I have marked the Iteration option
(automatically by VBA when worksheet is opening).
Thanks for your amendment.
Michael

I made an odd observation:

when using the code I presented, the application behaved on a PC with
english Win/Excel installation and on a PC with german Win/Excel
installation as intended; but when using Gary's "SendKeys"-technique,
the application on the german PC got weird: it scrolls down numerous
lines and do not updated the formulas.
Any ideas?

Nevertheless, using
sFormula = Rng1.Cells(j).FormulaR1C1
Rng1.Cells(j).FormulaR1C1 = sFormula
works.

Michael