0

I am a new user of vba and I am experiencing this error 1004 "Unable to set the FormulaArray property of the Range class" by running the following code:

Worksheets("Discriminatory_power").Range("G11").FormulaArray = "=INDEX(Import_data!A10:E1048576,MATCH(1,(Import_data!A10:A1048576=A11)*(Import_data!D10:D1048576=O8)*(Import_data!C10:C1048576=L7),0),MATCH(L4,Import_data!A10:F10,0))"

Can you please help me? Thank you

Vanessa
  • 1
  • Does this answer your question? [ERROR 1004 when trying to use FormulaArray. Replace trick does not work](https://stackoverflow.com/questions/51196290/error-1004-when-trying-to-use-formulaarray-replace-trick-does-not-work) – Foxfire And Burns And Burns Mar 17 '23 at 13:50

1 Answers1

0

A) For Array formula Every range needs absolute reference like below.

Worksheets("Discriminatory_power").Range("G11").FormulaArray = "=INDEX(Import_data!$A$10:$E$1048576,MATCH(1,(Import_data!$A$10:$A$1048576=A11)*(Import_data!$D$10:$D$1048576=O8)*(Import_data!$C$10:$C$1048576=L7),0),MATCH(L4,Import_data!$A$10:$F$10,0))"

B) If the Excel file format is Excell 97-2003 i.e. "XLS" then make sure to change the last row number from 1048576 to 65536.

Worksheets("Discriminatory_power").Range("G11").FormulaArray = "=INDEX(Import_data!$A$10:$E$65536,MATCH(1,(Import_data!$A$10:$A$65536=A11)*(Import_data!$D$10:$D$65536=O8)*(Import_data!$C$10:$C$65536=L7),0),MATCH(L4,Import_data!$A$10:$F$10,0))"
Dhay
  • 585
  • 7
  • 29