VBA: cannot automatically recalculate Excel formula after updating it — needs manual interaction

By | February 27, 2018
Questions:

In VBA, I am updating the formula in a cell (which works ok), but automatic recalculation does not work:

updated_formula = "=COUNT(Sheet1!A3:A" & nr_points & ")"
Cells(x, y).Formula = updated_formula
ActiveWorkbook.Save
Cells(x, y).Calculate

The formula simply counts the number of existing rows in another sheet. When I run the macro, the cell’s value in the function textfield is correct, but in the cell itself I have "#NAME?" and I need to press ENTER in the function to recalculate the formula.

Am I expecting too much of Excel? Or am I doing something wrong?

EDIT: Screenshot of the situation — this is what I see after running the macro.
(Sorry for the black censoring, have to maintain anonimity for the client company)

enter image description here

Answers:

After seeing your screenshot, it became clear.

Change

Cells(x, y).Formula = updated_formula

to

Cells(x, y).FormulaLocal = updated_formula

Questions:
Answers:

Your code works fine on my sheet and updates calculated value immediately after macro run. Please make sure Calculation Options are set to “Automatic”: Ribbon Data tab > Calculation Options > Automatic.

Questions:
Answers:

I know this is a late post, but I had a similar issue and what worked for me was to change

Cells(x, y).Calculate

to

Cells(x, y).Dirty

Leave a Reply

Your email address will not be published. Required fields are marked *