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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,915
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,002
Messages
5,545,440
Members
410,684
Latest member
LakTik
Top