Run-time error 1004 for ArrayFormula

[mucah!t]

Hello all,

The following code gives a runtime error and I can't figure out why.
I've tried other [test] formulas instead and they all worked.

Range("Z12").FormulaArray = "=IF(ROWS(R12:R)>SUM(--('Matrix Heftruck'!R2C44:R500C44>=R3C20)*('Matrix Heftruck'!R2C44:R500C44<=R3C21)),"""",INDEX('Matrix Heftruck'!R1:R500,--RIGHT(SMALL(IF(('Matrix Heftruck'!R2C44:R500C44>=R3C20)*('Matrix Heftruck'!R2C44:R500C44<=R3C21),--('Matrix Heftruck'!R2C44:R500C44 & RIGHT(""00000"" &ROW(R2C26:R491C26),5)),""""),ROWS(R12:R)),5),COLUMNS(C26:C)))"

When I enter the Formula manually in the Z12 it workes fine.

 

[mucah!t]

Thanks Andrew,

This one works.
When I extent the range to, lets say Z12:Z20, the references don't change with the cells.
Can this be solved?

 

[Andrew Poulsom]

You need to copy the formula in Z12 down.

 

[mucah!t]

You've really helped me a lot Andrew! Thanks!