On Feb 11, 11:49 am, Gary''s Student
wrote:
Sub re_assert()
Range("D10").Select
Application.SendKeys "{F2}"
Application.SendKeys "{ENTER}"
DoEvents
End Sub
--
Gary''s Student - gsnu2007L
"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
Gary, Mike thank you for answers.
@Mike: it is circular, since D10 is the cell in question.
Thank you for the web-link.
Here is my code I came up with, it cycles through all cells of row 10
and rebuilds formulas if cells have ones.
Public Sub RedoFormulae()
' Cycling through template row and rebuilding all of it's formula to
force hard recalculation
' Idea from ExcelFormu,
http://www.excelforum.com/excel-prog...ithout-f2.html
Dim sFormula As String
Dim NumCols As Long
Dim Rng1 As Range
Dim Rng2 As Range
Config.Range("A10").Select
Set Rng1 = Range(ActiveCell, Cells(ActiveCell.Row, Columns.Count).End
(xlToLeft))
Set Rng2 = Config.Range("A10").Resize(, Rng1.Columns.Count)
NumCols = Rng2.Count
For j = 1 To NumCols
' rebuild only cells with formulas
If Rng1.Cells(j).HasFormula Then
sFormula = Rng1.Cells(j).FormulaR1C1
Rng1.Cells(j).FormulaR1C1 = sFormula
End If
Next j
End Sub