Unable to set the FormulaArray property of the Range class

[breynolds0431]

Keep getting the unable to set the FormulaArray property of the Range class error. I've tested the formula by manually adding to a cell and it works fine. But, I can't seem to find what the issue is. Any ideas on what is tripping this up?

Sub Array ()

Dim theFormulaPart1 As String
Dim theFormulaPart2 As String
Dim theFormulaPart3 As String
Dim theFormulaPart4 As String
Dim theFormulaPart5 As String
Dim theFormulaPart6 As String

theFormulaPart1 = "=IFERROR(INDEX('[MEDataFiles.xlsb]1'!C2,MATCH(1,IF(RC3>='[MEDataFiles.xlsb]1'!C3,IF(RC3<='[MEDataFiles.xlsb]1'!C4,IF(RC2='[MEDataFiles.xlsb]1'!C1,1))),0)),IFERROR(INDEX('[MEDataFiles.xlsb]1'!C7,MATCH(1,IF(RC3>='[MEDataFiles.xlsb]1'!C8, BDR())"

theFormulaPart2 = "IF(RC3<='[MEDataFiles.xlsb]1'!C9,IF(RC2='[MEDataFiles.xlsb]1'!C6,1))),0)),IFERROR(INDEX('[MEDataFiles.xlsb]1'!C12,MATCH(1,IF(RC3>='[MEDataFiles.xlsb]1'!C13,IF(RC3<='[MEDataFiles.xlsb]1'!C14,IF(RC2='[MEDataFiles.xlsb]1'!C11,1))),0)),"""""""")))"


theFormulaPart3 = "=IFERROR(INDEX('[MEDataFiles.xlsb]1'!C3,MATCH(1,IF(RC3>='[MEDataFiles.xlsb]1'!C3,IF(RC3<='[MEDataFiles.xlsb]1'!C4,IF(RC2='[MEDataFiles.xlsb]1'!C1,1))),0)),IFERROR(INDEX('[MEDataFiles.xlsb]1'!C8,MATCH(1,IF(RC3>='[MEDataFiles.xlsb]1'!C8, BDR())"
    
theFormulaPart4 = "IF(RC3<='[MEDataFiles.xlsb]1'!C9,IF(RC2='[MEDataFiles.xlsb]1'!C6,1))),0)),IFERROR(INDEX('[MEDataFiles.xlsb]1'!C13,MATCH(1,IF(RC3>='[MEDataFiles.xlsb]1'!C13,IF(RC3<='[MEDataFiles.xlsb]1'!C14,IF(RC2='[MEDataFiles.xlsb]1'!C11,1))),0)),"""""""")))"


theFormulaPart5 = "=IFERROR(INDEX('[MEDataFiles.xlsb]1'!C4,MATCH(1,IF(RC3>='[MEDataFiles.xlsb]1'!C3,IF(RC3<='[MEDataFiles.xlsb]1'!C4,IF(RC2='[MEDataFiles.xlsb]1'!C1,1))),0)),IFERROR(INDEX('[MEDataFiles.xlsb]1'!C9,MATCH(1,IF(RC3>='[MEDataFiles.xlsb]1'!C8, BDR())"
    
theFormulaPart6 = "IF(RC3<='[MEDataFiles.xlsb]1'!C9,IF(RC2='[MEDataFiles.xlsb]1'!C6,1))),0)),IFERROR(INDEX('[MEDataFiles.xlsb]1'!C14,MATCH(1,IF(RC3>='[MEDataFiles.xlsb]1'!C13,IF(RC3<='[MEDataFiles.xlsb]1'!C14,IF(RC2='[MEDataFiles.xlsb]1'!C11,1))),0)),"""""""")))"


Set wsh = Worksheets("MEDataGrab")

    i = 9
 
    While wsh.Cells(i, 2) <> ""

    With wsh.Cells(i, 4)
    .FormulaArray = theFormulaPart1
    .Replace "BDR())", theFormulaPart2
    
    End With
        
    With wsh.Cells(i, 5)
        .FormulaArray = theFormulaPart3
        .Replace "BDR())", theFormulaPart4
    
    End With
    
    With wsh.Cells(i, 6)
        .FormulaArray = theFormulaPart5
        .Replace "BDR())", theFormulaPart6
    
    End With

    wsh.Cells(i, 7).FormulaR1C1 = _
        "=IF(RC[-5]="""","""",IF(LEN(RC[-3])>2,VLOOKUP(NUMBERVALUE(LEFT(RC[-3],2)),Funding,3,FALSE),VLOOKUP(RC[-3],Funding,3,FALSE)))"
        
        i = i + 1
 
    Wend

 

[Norie]

Can you post the formula you are trying to add to the cells?

 

[breynolds0431]

Yes, I have three different columns for the array formula. The error is only happening on the first columns, but I imagine it would do all columns if it were to skip.

Sorry if there's a better way to post these formulas...

1st Column (C4):

=IFERROR(INDEX('[MEDataFiles.xlsb]1'!C2,MATCH(1,IF(RC3>='[MEDataFiles.xlsb]1'!C3,IF(RC3<='[MEDataFiles.xlsb]1'!C4,IF(RC2='[MEDataFiles.xlsb]1'!C1,1))),0)),IFERROR(INDEX('[MEDataFiles.xlsb]1'!C7,MATCH(1,IF(RC3>='[MEDataFiles.xlsb]1'!C8,IF(RC3<='[MEDataFiles.xlsb]1'!C9,IF(RC2='[MEDataFiles.xlsb]1'!C6,1))),0)),IFERROR(INDEX('[MEDataFiles.xlsb]1'!C12,MATCH(1,IF(RC3>='[MEDataFiles.xlsb]1'!C13,IF(RC3<='[MEDataFiles.xlsb]1'!C14,IF(RC2='[MEDataFiles.xlsb]1'!C11,1))),0)),"""""""")))

2nd Column (C5):

=IFERROR(INDEX('[MEDataFiles.xlsb]1'!C3,MATCH(1,IF(RC3>='[MEDataFiles.xlsb]1'!C3,IF(RC3<='[MEDataFiles.xlsb]1'!C4,IF(RC2='[MEDataFiles.xlsb]1'!C1,1))),0)),IFERROR(INDEX('[MEDataFiles.xlsb]1'!C8,MATCH(1,IF(RC3>='[MEDataFiles.xlsb]1'!C8, IF(RC3<='[MEDataFiles.xlsb]1'!C9,IF(RC2='[MEDataFiles.xlsb]1'!C6,1))),0)),IFERROR(INDEX('[MEDataFiles.xlsb]1'!C13,MATCH(1,IF(RC3>='[MEDataFiles.xlsb]1'!C13,IF(RC3<='[MEDataFiles.xlsb]1'!C14,IF(RC2='[MEDataFiles.xlsb]1'!C11,1))),0)),"""""""")))

3rd Column (C6):

=IFERROR(INDEX('[MEDataFiles.xlsb]1'!C4,MATCH(1,IF(RC3>='[MEDataFiles.xlsb]1'!C3,IF(RC3<='[MEDataFiles.xlsb]1'!C4,IF(RC2='[MEDataFiles.xlsb]1'!C1,1))),0)),IFERROR(INDEX('[MEDataFiles.xlsb]1'!C9,MATCH(1,IF(RC3>='[MEDataFiles.xlsb]1'!C8,IF(RC3<='[MEDataFiles.xlsb]1'!C9,IF(RC2='[MEDataFiles.xlsb]1'!C6,1))),0)),IFERROR(INDEX('[MEDataFiles.xlsb]1'!C14,MATCH(1,IF(RC3>='[MEDataFiles.xlsb]1'!C13,IF(RC3<='[MEDataFiles.xlsb]1'!C14,IF(RC2='[MEDataFiles.xlsb]1'!C11,1))),0)),"""""""")))