Unable to set the FormulaArray property of the Range class Error

[yoan6]

I hope someone can help me with this. When I manually enter this array formula into a cell using Ctrl-Shift-Enter, everything works fine:

=IF(ISNA(INDEX(Log_WK!A:A;MATCH(SLA!B5;IF(VLOOKUP(E5; Correspondance!A2:B173; 2; FALSE)=Log_WK!C:C;Log_WK!D:D);0)));"";INDEX(Log_WK!A:A;MATCH(SLA!B5;IF(VLOOKUP(E5; Correspondance!A2:B173; 2; FALSE)=Log_WK!C:C;Log_WK!D:D);0)))


But, when I run this same event with code, the following code:

Selection.FormulaArray = "=IF(ISNA(INDEX(Log_WK!C[-5],MATCH(SLA!RC[-4],IF(VLOOKUP(RC[-1], Correspondance!RC[-5]:R[171]C[-4], 2, FALSE)=Log_WK!C[-3],Log_WK!C[-2]),0))),"""",INDEX(Log_WK!C[-5],MATCH(SLA!RC[-4],IF(VLOOKUP(RC[-1], Correspondance!RC[-5]:R[171]C[-4], 2, FALSE)=Log_WK!C[-3],Log_WK!C[-2]),0)))"

...DOES NOT WORK. I get an error msg: "Unable to set the FormulaArray property of the Range class"

I know that is due to the fact that I have more than 255 characters.

So could you help me to split this formula.

 

[wigi]

Hello

Maybe you can use IFNA(...) instead of IF(ISNA( ... ))
provided you use Excel 2007 or later.

 

[yoan6]

I do not have this function with my Excel version.

 

[VoG]

This may help you Daily Dose of Excel » Blog Archive » Entering Long Array Formulas in VBA