VBA运行时错误1004:无法设置范围类的FormulaArray属性 [英] VBA Run time error 1004: Unable to set the formulaarray property of the range class

问题描述

VBA运行时错误1004:无法设置范围类的FormulaArray属性

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

在

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.

推荐答案

正如我在评论中所提到的，它只是公式第一部分的长度.我建议您进一步简化:

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运行时错误1004:无法设置范围类的FormulaArray属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！