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

%3CLINGO-SUB%20id%3D%22lingo-sub-2489004%22%20slang%3D%22en-US%22%3ERun%20Time%20Error%201004-%20Long%20Array%20Formula%20in%20VBA%20(How%20to%20split%20the%20code%20in%20VBA)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2489004%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHello%20Everyone%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20am%20trying%20to%20automate%20INDEX%20using%20Array%20(Ctrl%2BShift%2BEnter)%20through%20macro.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3Ebut%20when%20I%20run%20the%20command%2C%20run%20time%20error%201004%20appears%20and%20the%20code%20is%20highlighted%20with%20yellow%20color.%20This%20code%20has%20500%2B%20characters.%20please%20help%20me%20how%20to%20split%20the%20code%20in%20VBA.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20am%20new%20to%20VBA%2C%20please%20help%20write%20the%20code%20(of%20less%20than%20255%20character)%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EIndex%20formula%20between%20sheet1%20and%20sheet3%20is%20mentioned%20below.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3E%3D%7BINDEX(Sheet1!%24X%242%3A%24X%246500%2CMATCH(1%2C(Sheet3!A3%3DSheet1!%24A%242%3A%24A%246500)*(Sheet3!B3%3DSheet1!%24B%242%3A%24B%246500)*(Sheet3!C3%3DSheet1!%24C%242%3A%24C%246500)*(Sheet3!D3%3DSheet1!%24D%242%3A%24D%246500)*(Sheet3!E3%3DSheet1!%24E%242%3A%24E%246500)*(Sheet3!F3%3DSheet1!%24F%242%3A%24F%246500)*(Sheet3!G3%3DSheet1!%24G%242%3A%24G%246500)*(Sheet3!H3%3DSheet1!%24H%242%3A%24H%246500)*(Sheet3!AJ3%3DSheet1!%24AJ%242%3A%24AJ%246500)*(Sheet3!AO3%3DSheet1!%24AO%242%3A%24AO%246500)*(Sheet3!AP3%3DSheet1!%24AP%242%3A%24AP%246500)*(Sheet3!AQ3%3DSheet1!%24AQ%242%3A%24AQ%246500)%2C0))%7D%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EBelow%20is%20the%20program%20that%20appeared%20in%20VBA%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3ESub%20Macro3()%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E'%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E'%20Macro3%20Macro%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E'%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3E'%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ESheets(%22Sheet3%22).Select%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ERange(%22X2%22).Select%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ESelection.FormulaArray%20%3D%20_%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%22%3DINDEX(Sheet1!R2C24%3AR6500C24%2CMATCH(1%2C(Sheet3!RC%5B-23%5D%3DSheet1!R2C1%3AR6500C1)*(Sheet3!RC%5B-22%5D%3DSheet1!R2C2%3AR6500C2)*(Sheet3!RC%5B-21%5D%3DSheet1!R2C3%3AR6500C3)*(Sheet3!RC%5B-20%5D%3DSheet1!R2C4%3AR6500C4)*(Sheet3!RC%5B-19%5D%3DSheet1!R2C5%3AR6500C5)*(Sheet3!RC%5B-18%5D%3DSheet1!R2C6%3AR6500C6)*(Sheet3!RC%5B-17%5D%3DSheet1!R2C7%3AR6500C7)*(Sheet3!RC%5B-16%5D%3DSheet1!R2C8%3AR6500C8)*(Sheet3!RC%5B12%5D%3DSheet1!R2C36%3AR6500C3%22%20%26amp%3B%20_%3CBR%20%2F%3E%226)*(Sheet3!RC%5B17%5D%3DSheet1!R2C41%3AR6500C41)*(Sheet3!RC%5B18%5D%3DSheet1!R2C42%3AR6500C42)*(Sheet3!RC%5B19%5D%3DSheet1!R2C43%3AR6500C43)%2C0))%22%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ERange(%22X2%22).Select%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ESelection.End(xlDown).Select%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ERange(%22X6089%22).Select%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ESelection.End(xlUp).Select%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ERange(%22X2%22).Select%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ESelection.FillDown%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ERange(%22X2%22).Select%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EEnd%20Sub%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2489004%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

Hello Everyone,

I am trying to automate INDEX using Array (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.

I am new to VBA, please help write the code (of less than 255 character)

Index formula between sheet1 and sheet3 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 appeared 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

1 Reply