How to add hardcoded float number values to the fo

Question

The folllowing VBA code doesn't work: (I assume that addedvalue is rational numer)

Dim addedvalue As Double
addedvalue = 1
ActiveCell.FormulaR1C1 = ActiveCell.FormulaR1C1 & "+" & CStr(addedvalue)

However this one works fine:

ActiveCell.FormulaR1C1 = ActiveCell.FormulaR1C1 & "+" & 1

Unfortunately, I must have addedvalue as a variable.

I was trying also ActiveCell.FormulaR1C1 = ActiveCell.FormulaR1C1 & "+" & addedvalue but it also doesn't work.

The error message I gor is: enter image description here

---

UPDATE The problem is why this doesn't work:

Sub added()
Dim rational As Double
rational = 1.1
ActiveCell.FormulaR1C1 = ActiveCell.FormulaR1C1 & "+" & rational
End Sub

Answer 1

For everyone, using comma as a decimal separator, the VBEditor has a small "feature". Long story short, try the following:

Sub TestMe()

    Dim addedvalue As Double
    addedvalue = 1.2
    ActiveCell.FormulaR1C1 = ActiveCell.FormulaR1C1 & "+" & Replace(addedvalue, ",", ".")

End Sub

This is my custom function, that I use for cases like this (and they are quite a lot):

Public Function fnStrChangeCommas(ByVal myValue As Variant) As String
    fnStrChangeCommas = Replace(CStr(myValue), ",", ".")
End Function

Answer 2

Tried it and works just fine:

Sub Test()
    Dim addedValue As Integer
    addedValue = 1
    ActiveCell.FormulaR1C1 = ActiveCell.FormulaR1C1 & "+" & addedValue
End Sub

Answer 3

If you are not using a US-EN version of Excel, you have two easy options:

1. Format your number in a US-EN format prior to appending it to the formula, or
2. Write the formula in your own locale's format

The answer by Vityata gives an example of how to use option 1.

Option 2 would look something like:

Sub added()
    Dim rational As Double
    rational = 1.1
    ActiveCell.FormulaLocal = ActiveCell.FormulaLocal & "+" & rational
End Sub