VBA Run time error 1004: Unable to set the formulaarray property of the range class

Question

I've followed Dick Kusleika's advice in this link, but can't get the following array formula to enter into excel via VBA. Can anyone see where I'm going wrong? Both halves of the formula are easily below 255 characters.

Public Sub Configuration()

Dim theFormulaPart1 As String

Dim theFormulaPart2 As String

theFormulaPart1 = "=IF(ISODD(B2),IFERROR(INDEX(Race1Grid,MATCH(C2&I2&""Q3""," & _
                "QualRace1ID&QualDriver&QSession,0)),IFERROR(INDEX(Race1Grid" & _
                ",MATCH(C2&I2&""Q2"",QualRace1ID&QualDriver&QSession,0)),INDEX" & _
                "(Race1Grid,MATCH(C2&I2,QualRace1ID&QualDriver,0))))," & _
                "X_X_X())"

theFormulaPart2 = "IFERROR(INDEX(Race2Grid,MATCH(C2&I2&""Q3"",QualRace2ID&" & _
                "QualDriver&QSession,0)),IFERROR(INDEX(Race2Grid,MATCH(C2&" & _
                "I2&""Q2"",QualRace2ID&QualDriver&QSession,0)),INDEX(" & _
                    "Race2Grid,MATCH(C2&I2,QualRace2ID&QualDriver,0)))))"

    With Worksheets("Races").Range("V2")
        .FormulaArray = theFormulaPart1
        .Replace "X_X_X())", theFormulaPart2
    End With

End Sub

I have tried splitting the formula onto more lines to make it easier to read.

You need to make sure that it's under 255 in R1C1 format too. Yours isn't - it's about 279. — Rory

Rory Rory · Accepted Answer · 2016-11-18T14:37:09

As I mentioned in the comments, it's just the length of the first part of the formula. I suggest you simplify further:

Public Sub Configuration()

Dim theFormulaPart1 As String

Dim theFormulaPart2 As String

Dim theFormulaPart3 As String

theFormulaPart1 = "=IF(ISODD(B2),X_X_X1(),X_X_X())"
theFormulaPart3 = "IFERROR(INDEX(Race1Grid,MATCH(C2&I2&""Q3""," & _
                "QualRace1ID&QualDriver&QSession,0)),IFERROR(INDEX(Race1Grid" & _
                ",MATCH(C2&I2&""Q2"",QualRace1ID&QualDriver&QSession,0)),INDEX" & _
                "(Race1Grid,MATCH(C2&I2,QualRace1ID&QualDriver,0)))),"
theFormulaPart2 = "IFERROR(INDEX(Race2Grid,MATCH(C2&I2&""Q3"",QualRace2ID&" & _
                "QualDriver&QSession,0)),IFERROR(INDEX(Race2Grid,MATCH(C2&" & _
                "I2&""Q2"",QualRace2ID&QualDriver&QSession,0)),INDEX(" & _
                    "Race2Grid,MATCH(C2&I2,QualRace2ID&QualDriver,0)))))"

    With ActiveSheet.Range("V2")
        .FormulaArray = theFormulaPart1
        .Replace "X_X_X())", theFormulaPart2
        .Replace "X_X_X1()", theFormulaPart3
    End With

End Sub

VBA Run time error 1004: Unable to set the formulaarray property of the range class

1 Answers