How do I solve Error 1004 when using "FormulaR1C1"

user_15633989 0
Viewed 151

I'm trying to get a script working using .FormulaR1C1

Dim blatt As Worksheet
Set blatt = ThisWorkbook.Worksheets("Kurse")
Dim i As Integer
Dim j As Integer
Dim beginn As Integer
Dim schluss As Integer
Dim vlookuprowstart As Integer
Dim vlookuprowend As Integer
Dim referencecolumn As Integer

j = 2
beginn = -1
schluss = 0

For j = 2 To 6597
    referencecolumn = 1 - j
    i = 5
    For i = 5 To 7
        vlookuprowstart = 6 - i
        vlookuprowend = 64 - i
        blatt.Cells(i, j).FormulaR1C1 = "=vlookup($R[0]C[referencecolumn];Aktienkurse!R[vlookuprowstart]C[beginn]:r[vlookuprowend]C[schluss];2;false)"
        beginn = beginn + 1
        schluss = schluss + 1
        i = i + 1
    Next i
Next j

However, when trying to execute the FormulaR1C1 command, I get error 1004 - application-defined or object-defined error

Would be great, if somebody could help me. I hope I gave any information necessary.

runtime-error vba
1 Answers

Germany uses a bit different settings than the standard ones in VBA. Thus , is ; in formulas, . is a , as decimal separator. In your formula, you are using ;, which is not ok.

Best case scenario - write the formula in Excel cell, then select the cell with the mouse. Then run this code:

Sub RunThis
    MsgBox ActiveCell.FormulaR1C1 
End Sub

and try to fix the error, by adjusting what you see to your code.

After doing this, start working on this - R[0]C[referencecolumn]. The referencecolumn should be a variable and not a string. E.g., something like this works on an empty worksheet, just to get the idea:

Sub TestMe()
    
    Dim rowEnd As Long
    rowEnd = 10
    Dim rowStart As Long
    rowStart = 1
    
    Range("B1") = "=SUM(R[" & rowStart & "]C[-1]:R[" & rowEnd & "]C[-1])"

End Sub

Examine the formula in "B1" and find a way to modify rowStart and rowEnd to something, that will have more meaning for you.

user_54486260
Related