Add a cell formula in Excel via vba

Question

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

Current Excel formula:

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

VBA formula :

 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?

Solution 1

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

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.

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

Solution 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:

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