VBA：更新后不能自动重新计算Excel公式 - 需要手动交互 [英] VBA: cannot automatically recalculate Excel formula after updating it -- needs manual interaction

问题描述

在VBA中，我正在更新单元格中的公式（可以正常工作），但是自动重新计算不起作用：

  updated_formula == COUNT（Sheet1！A3：A& nr_points&）
细胞（x，y）.Formula = updated_formula 
 ActiveWorkbook.Save 
细胞（x， y）。计算
  

公式只是计算另一个工作表中现有行的数量。当我运行宏时，单元格在函数textfield中的值是正确的，但是在单元本身中，我有＃NAME，我需要按 ENTER

我期望Excel中有太多的数据？或者我做错了什么？

编辑：情况的截图 - 这是运行宏后看到的。
（对于黑色审查，对于客户公司来说，必须保持一致）

解决方案

看到你的屏幕截图后， / p>

更改

单元格（x，y）.Formula = updated_formula

to

Cells（x，y）.FormulaLocal = updated_formula

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.

Change

Cells(x, y).Formula = updated_formula

to

Cells(x, y).FormulaLocal = updated_formula

这篇关于VBA：更新后不能自动重新计算Excel公式 - 需要手动交互的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！