FormulaR1C1 returning nothing when using a variable

[Zem32619]

Hi All.

I have a code that works perfectly fine for users with the US excel and windows setting. I'm stuck why FormulaR1C1 doesn't work as it should when ran in a German setting.
Here is the line of code that works for US setting.

Range("L2").FormulaR1C1 = "=(1-(RC[14])- " & ACoS & " ))*RC[-1]"

But it returns nothing, as in empty when ran in a German setting. I'm thinking if it is the variable thing "ACoS".

Would really appreciate any help. Thank you so much in advance.

 

[Zem32619]

@StephenCrump & @Michael M thank you so much for all your thoughts here, very much appreciated. Will play around the code with your suggestions and get back here if it works or not.
Again, thank you.

 

[Alex Blakenburg]

You only need to read this is Michael & Stephen's suggestions don't fix it.

Hi Alex,
Yes, the Use system Separator is turned on.
Thank you for your reply.

I am really surprised that this is the case since your code is turning it off and I can't see where it gets turned back on.
(I would love to see an image of say column K before and after running the code to see what this is doing to the numbers.)

    With Application
        .DecimalSeparator = "."
        .ThousandsSeparator = ","
        .UseSystemSeparators = False
        Columns("K:K").Replace What:=",", Replacement:="."
        Columns("T:T").Replace What:=",", Replacement:="."
        Columns("U:U").Replace What:=",", Replacement:="."
        Columns("V:V").Replace What:=",", Replacement:="."
        Columns("X:X").Replace What:=",", Replacement:="."
        Columns("Y:Y").Replace What:=",", Replacement:="."
    End With

Again assuming it is not fixed yet. Can you add the code below to before the line it is erroring out at.
Once run please provide and image of the Message box and also an image of what is in L2.

    '---------------------------------------------------------------------------------------------
    'Testing parameters
    '---------------------------------------------------------------------------------------------
    Dim sMsg As String
    Dim tempUseSysSet
    
    With Application
        sMsg = "ACoS: " & vbTab & vbTab & vbTab & vbTab & ACoS & vbLf & _
                "UseSystemSeparator: " & vbTab & vbTab & .UseSystemSeparators & vbLf
        tempUseSysSet = .UseSystemSeparators
        If .UseSystemSeparators = False Then
            .UseSystemSeparators = True
        End If
        
         sMsg = sMsg & _
                "Windows Decimal Separator: " & vbTab & .International(xlDecimalSeparator) _
                    & vbTab & Switch(.International(xlDecimalSeparator) = ",", "Comma", .International(xlDecimalSeparator) = ".", "Full Stop") & vbLf _
                & "Excel Decimal Separator: " & vbTab & vbTab & .DecimalSeparator _
                    & vbTab & Switch(.DecimalSeparator = ",", "Comma", .DecimalSeparator = ".", "Full Stop") & vbLf _
                & "=(1-(RC[14])-" & ACoS & ")*RC[-1]"
        .UseSystemSeparators = tempUseSysSet
        
    End With
    
    MsgBox sMsg
    
    Range("L2").Value = "'" & "=(1-(RC[14])-" & ACoS & ")*RC[-1]"       'if .Formula errors out show this
    '---------------------------------------------------------------------------------------------
    
    ' ADD THE ABOVE BEFORE THIS LINE
    Range("L2").Formula = "=(1-(RC[14])-" & ACoS & ")*RC[-1]"

On my screen it looks like this.