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)
After seeing your screenshot, it became clear.
Cells(x, y).Formula = updated_formula
Cells(x, y).FormulaLocal = updated_formula
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.
I know this is a late post, but I had a similar issue and what worked for me was to change