Excel VBA - 1004 run-time error, Application or object-defined error

I'm trying to go through a range of cells in a worksheet and write a formula in each one. But I keep getting the error:

Run-time error '1004'

Application-defined or object-defined error

The code looks like this right now:

Sub updateFormulasForNamedRange()
    'Application.Calculation = xlCalculationManual
    'Application.ScreenUpdating = False

    Dim row, col, fieldCount As Integer
    colCount = 13
    RowCount = 60

    For col = 1 To 开发者_运维知识库colCount
        For row = 1 To RowCount
            Dim strColCharacter

            If col > 26 Then
                strColCharacter = Chr(Int((row - 1) / 26) + 64) & Chr(((row - 1) Mod 26) + 65)
            Else
                strColCharacter = Chr(row + 64)
            End If

            Worksheets("Rawdata1").Cells(row, col).FormulaR1C1 = "=IF(Numbers1!$E$" & col & "<>0;Numbers1!" & "$" & strColCharacter & "$" & row & ";"""")"

        Next row
    Next col

    'Application.Calculation = xlCalculationAutomatic
    'Application.ScreenUpdating = True
End Sub

It fails at the line where you assign the formula to the cell. I tried to replace the string with just "test" and it works. But it's this string that's not accepted. Even though it's the exact same string that's currently in the formula bar of that exact cell. And the string looks fine to me?

"=IF(Numbers1!$E$1<>0;Numbers1!$A$1;"")"

I don't quite know the difference of all the Formula properties, but I've tried a variant of them and all throw the same error. So what could be causing this error?

Your problem is with .FormulaR1C1. This tells the formula to expect a Row Number, Column Number style formula reference, but then you give it an address (Column, Row) style formula.

Change .FormulaR1C1 to .Formula

Error was in the string:

Worksheets("Rawdata1").Cells(row, col).FormulaR1C1 = "=IF(Numbers1!$E$" & col & "<>0;Numbers1!" & "$" & strColCharacter & "$" & row & ";"""")"

Should have been:

Worksheets("Rawdata1").Cells(row, col).FormulaR1C1 = "=IF(Numbers1!$E$" & row & "<>0;Numbers1!" & "$" & strColCharacter & "$" & row & ";"""")"

Targeting the row, not the column.