通过vba在Excel中添加单元格公式

I’m not an Excel or VBA expert but I want to insert this current excel formula into cell’s using VBA.

我不是Excel或VBA专家，但我想使用VBA将此当前excel公式插入到单元格中。

Current Excel formula:

当前的Excel公式：

=IF(OR(ISNUM(D570)=FALSE;ISNUM(D573)=FALSE);"";IF(C573="Total";D573-D570;""))

VBA formula :

VBA公式：

 ActiveSheet.Range("a" & ActiveSheet.Rows.Count).End(xlUp).Offset(2, 12).Value = "=IF(OR(ISNUM(R[-3]C[-9])=FALSE;ISNUM(R[0]C[-9])=FALSE);'';IF(R[0]C[-10]='Total';R[0]C[-9]-R[-3]C[-9];''))"

It doesn’t work… Someone can help me please?

它不起作用......有人可以帮我吗？

2 个解决方案

#1

Try using .formula = instead of .value = in your VBA code.

尝试在您的VBA代码中使用.formula =而不是.value =。

Setting the .value of a cell simply copies in whatever value you specify. In this case, your formula is simply converted to a string value.

设置单元格的.value只需复制您指定的任何值。在这种情况下，您的公式只是转换为字符串值。

Using the .formula property, you are actually specifying the formula that gets used to compute the value, which is what you are looking for.

使用.formula属性，实际上是指定用于计算值的公式，这是您要查找的值。

#2

Can I first suggest a simplification of your formula, from:

我可以先建议简化您的配方，来自：

=IF(OR(ISNUM(D570)=FALSE;ISNUM(D573)=FALSE);"";IF(C573="Total";D573-D570;""))

...to...

...至...

=IF(AND(C573="Total"; ISNUM(D570); ISNUM(D573)); D573-D570; "")

Then, I'd set a cell (the active cell in the example below) to use that formula using the VBA code:

然后，我设置一个单元格（下面示例中的活动单元格）使用VBA代码使用该公式：

ActiveCell.Formula = "=IF(...)"

#1