Unable to set the FormulaArray property of the Range class

breynolds0431

Board Regular
Joined
Feb 15, 2013
Messages
66
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?


VBA Code:
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
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,941
Office Version
  1. 365
Platform
  1. Windows
Can you post the formula you are trying to add to the cells?
 

breynolds0431

Board Regular
Joined
Feb 15, 2013
Messages
66
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)),"""""""")))
 

Watch MrExcel Video

Forum statistics

Threads
1,114,542
Messages
5,548,635
Members
410,861
Latest member
Victor96
Top