Runtime error 1004

[Misca]

I'm trying to run a macro that replaces a decimal value in a cell with a formula that refers to that number:

Dim cell As Range

For Each cell In Selection
    If IsNumeric(cell) Then
        cell.Formula = "=ROUND(" & cell.Value & "*(1+$D$5), 2)"
    End If
Next cell

,
When I run the macro I'm getting "Run-time error '1004': Application-defined or object-defined error". I believe this is because I'm using the Finnish settings in my Excel where the decimal separator is a comma instead of a period. I believe the IsNumeric should make sure VBA understands the numbers as numbers even with the commas but I'm not getting an error if I replace the commas with periods. Is there any way to use the actual cell value regardless of the formatting instead of replacing the "cell.value" with the "Replace(cell.value, ",", ".")" wich to me seems unnecessary for VBA clearly understands the cell value as a number instead of text already.

 

[nemmi69]

This is Excel not VBa. Seems like it is set with "." as the decimal point.

 

[nemmi69]

Playing with the below you can set as required

Sub Seperator()
With Application
    .UseSystemSeparators = False
    .ThousandsSeparator = "."
    Debug.Print [A1].Text
  
    '// Go Back to System
    .ThousandsSeparator = ","
    .UseSystemSeparators = True
    Debug.Print [A1].Text
End With
  
End Sub

 

[RoryA]

If you use FormulaLocal instead (adjusting separators and function names to match what you would actually enter in a cell), it should work with your regional settings.