Runtime Error 1004 when replacing formula

Question

I am using the following code to add something to a Formula in VBA (for debugging purposes I am using the variable currentFormula, instead of doing it directly):

currentFormula = Range("A" & row).Formula
currentFormula = currentFormula & "+" & CStr(difference)
Range("A" & row).Formula = currentFormula

When going through the code step by step, the variable currentFormula has the correct value before the final step, e.g. "=A1/A2+0.5". However, then the script Fails with runtime error 1004. When I am Setting currentFormula manually to something like "=10+10", the script works.

Answer 1

CStr formats the number according to the current system locale.
Formula accepts formulas in English.

The function that converts numbers to strings in an invariant way is Str. Note that it prepends a space to positive numbers which you might want to remove:

currentFormula = currentFormula & "+" & LTrim$(Str$(difference))