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
