Run Time Error 1004- Long Array Formula in VBA (How to split the formula)

[Shamis Shahab]

Hello Everyone,

I am trying to automate INDEX formula using Array formula (Ctrl+Shift+Enter) through macro.
but when I run the command, run time error 1004 appears and the code is highlighted with yellow color. This code has 500+ characters. please help me how to split the code in VBA.

Index formula for excel is mentioned below.

={INDEX(Sheet1!$X$2:$X$6500,MATCH(1,(Sheet3!A3=Sheet1!$A$2:$A$6500)*(Sheet3!B3=Sheet1!$B$2:$B$6500)*(Sheet3!C3=Sheet1!$C$2:$C$6500)*(Sheet3!D3=Sheet1!$D$2:$D$6500)*(Sheet3!E3=Sheet1!$E$2:$E$6500)*(Sheet3!F3=Sheet1!$F$2:$F$6500)*(Sheet3!G3=Sheet1!$G$2:$G$6500)*(Sheet3!H3=Sheet1!$H$2:$H$6500)*(Sheet3!AJ3=Sheet1!$AJ$2:$AJ$6500)*(Sheet3!AO3=Sheet1!$AO$2:$AO$6500)*(Sheet3!AP3=Sheet1!$AP$2:$AP$6500)*(Sheet3!AQ3=Sheet1!$AQ$2:$AQ$6500),0))}


Below is the program that appears in VBA

Sub Macro3()
'
' Macro3 Macro
'

'
Sheets("Sheet3").Select
Range("X2").Select
Selection.FormulaArray = _
"=INDEX(Sheet1!R2C24:R6500C24,MATCH(1,(Sheet3!RC[-23]=Sheet1!R2C1:R6500C1)*(Sheet3!RC[-22]=Sheet1!R2C2:R6500C2)*(Sheet3!RC[-21]=Sheet1!R2C3:R6500C3)*(Sheet3!RC[-20]=Sheet1!R2C4:R6500C4)*(Sheet3!RC[-19]=Sheet1!R2C5:R6500C5)*(Sheet3!RC[-18]=Sheet1!R2C6:R6500C6)*(Sheet3!RC[-17]=Sheet1!R2C7:R6500C7)*(Sheet3!RC[-16]=Sheet1!R2C8:R6500C8)*(Sheet3!RC[12]=Sheet1!R2C36:R6500C3" & _
"6)*(Sheet3!RC[17]=Sheet1!R2C41:R6500C41)*(Sheet3!RC[18]=Sheet1!R2C42:R6500C42)*(Sheet3!RC[19]=Sheet1!R2C43:R6500C43),0))"
Range("X2").Select
Selection.End(xlDown).Select
Range("X6089").Select
Selection.End(xlUp).Select
Range("X2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
Range("X2").Select
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
Why not use a non array entered like

   Range("X2").Formula2R1C1 = _
        "=INDEX(Sheet1!R2C24:R6500C24,AGGREGATE(15,6,(ROW(Sheet1!R2C24:R6500C24)-ROW(Sheet1!R2C24)+1)/(RC[-23]=Sheet1!R2C1:R6500C1)/(RC[-22]=Sheet1!R2C2:R6500C2)/" & _
        "(RC[-21]=Sheet1!R2C3:R6500C3)/(RC[-20]=Sheet1!R2C4:R6500C4)/(RC[-19]=Sheet1!R2C5:R6500C5)/(RC[-18]=Sheet1!R2C6:R6500C6)/(RC[-17]=Sheet1!R2C7:R6500C7)/" & _
        "(RC[-16]=Sheet1!R2C8:R6500C8)/(RC[12]=Sheet1!R2C36:R6500C36)/(RC[17]=Sheet1!R2C41:R6500C41)/(RC[18]=Sheet1!R2C42:R6500C42)/(RC[19]=Sheet1!R2C43:R6500C43),1))"

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Run Time Error 1004- Long Array Formula in VBA (How to split the VBA code)
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.