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.

 

[Andrew Poulsom]

This doesn't look right:

ROWS(R12:R).....COLUMNS(C26:C)

What does the macro recorder give you if you enter the formula manually?

 

[mucah!t]

When recording the formula no error occurs.
It's only after trying to run the macro the error occurs.
This is the formula

=IF(ROWS($12:12)>SUM(--('Matrix Heftruck'!$AR$2:$AR$500>=$T$3)*('Matrix Heftruck'!$AR$2:$AR$500<=$U$3)),"",INDEX('Matrix Heftruck'!$1:$500,--RIGHT(SMALL(IF(('Matrix Heftruck'!$AR$2:$AR$500>=$T$3)*('Matrix Heftruck'!$AR$2:$AR$500<=$U$3),--('Matrix Heftruck'!$AR$2:$AR$500 & RIGHT("00000" &ROW($Z$2:$Z$491),5)),""),ROWS($12:12)),5),COLUMNS($Z:Z)))

 

[Andrew Poulsom]

The formula is too long (max 255 characters). Try:

    With Range("Z12")
        .FormulaArray = "=IF(ROWS(R12:R)>SUM(--('Matrix Heftruck'!R2C44:R500C44>=R3C20)*('Matrix Heftruck'!R2C44:R500C44<=R3C21)),""""," & "TheRest)"
        .Replace "TheRest)", "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)))"
    End With

 

[mucah!t]

When recording it manually it says "unable to record" [I am using 2003]
In 2010 however, where I recorded it, it does not give an error.

 

[Andrew Poulsom]

Did you try the code I posted?

 

[mucah!t]

Hi Andrew,

It seems to work partially.
It results in a #NAME?
The second is missing

=IF(ROWS($12:12)>SUM(--('Matrix Heftruck'!$AR$2:$AR$500>=$T$3)*('Matrix Heftruck'!$AR$2:$AR$500<=$U$3)),"",TheRest)

 

[Andrew Poulsom]

That happened for me once, but it worked eventually - still working now.

 

[mucah!t]

I have tried it many times. closed the file reopened it etc. but nothing?
Not to be rude, but shouldn't it just work the first time if it were correct?

 

[Andrew Poulsom]

I realise why it worked for me now. Try:

    Application.ReferenceStyle = xlR1C1
    With Range("Z12")
        .FormulaArray = "=IF(ROWS(R12:R)>SUM(--('Matrix Heftruck'!R2C44:R500C44>=R3C20)*('Matrix Heftruck'!R2C44:R500C44<=R3C21)),""""," & "TheRest)"
        .Replace "TheRest)", "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)))"
    End With
    Application.ReferenceStyle = xlA1