Array formula in a macro

[zerog]

Hi everybody,

I’m not an expert in vba, I have to write a macro that calculate a long array formula in a cell. I wrote:

Cells(2, LastCol + 1).Select
Selection.FormulaArray = "=IF(ISERROR(INDEX('[file.xlsx]Sheet'!R3C6:R23C8,MATCH(RC[-164]&RC[-159],'[file.xlsx]Sheet'!R3C7:R23C7&'[file.xlsx]Sheet'!R3C6:R23C6,0),3)),VLOOKUP(RC[-159],'[file.xlsx]Sheet'!R3C2:R29C3,2,0),INDEX('[file.xlsx]Sheet'!R3C6:R23C8,MATCH(RC[-164]&RC[-159],'[file.xlsx]Sheet'!R3C7:R23C7&'[file.xlsx]Sheet'!R3C6:R23C6,0),3))"

But I get the tipical error message:

Run-time error ‘1004’:
Application-defined or object-defined error

The funny thing is that if I use the same formula with Selection.FormulaR1C1 = at the beginning of the line it works (but unfortunately is then not defined as array formula)

Any suggestion?
Thanks a lot

[judgeh59]

my guess is you can't use RC formatting when not using FormulaR1C1...so you have to convert your RC references to something else...without seeing all the data I can't tell you what that would be...

[LJMetzger]

Hi zerog,

I have very little exposure to array formulas. However, the following link may help you: https://colinlegg.wordpress.com/2012...aarray-in-vba/

Lewis