VBA Array Formula

[jamescooper]

Hello,

The following code I have recorded as an array formula but it runs as a run-time error 1004. It is 246 characters, so under the limit of 256 - any ideas why it will not work please?

    Range("T10").FormulaArray = "=AVERAGE(IF(ROW(R2C16:R5000C16)<=SMALL(IF(ISNUMBER(R2C16:R5000C16),IF(R2C2:R5000C2=VALUE(R[-7]C[-1]),ROW(R2C16:R5000C16))),MIN(SUM(IF(R2C2:R5000C2=VALUE(R[-7]C[-1]),IF(ISNUMBER(R2C16:R5000C16),1))),5)),IF(R2C2:R5000C2=VALUE(R[-7]C[-1]),IF(ISNUMBER(R2C16:R5000C16),R2C16:R5000C16))))"

 

[Domenic]

Actually, it looks like it's 282 characters in length. So it exceeds the limit. Try the following instead...

    Dim strFormulaPart1 As String
    Dim strFormulaPart2 As String

    strFormulaPart1 = "SMALL(IF(ISNUMBER($P$2:$P$5000),IF($B$2:$B$5000=VALUE(S3),ROW($P$2:$P$5000))),Y_Y_Y)"
    strFormulaPart2 = "MIN(SUM(IF($B$2:$B$5000=VALUE(S3),IF(ISNUMBER($P$2:$P$5000),1))),5)"

    With Range("T10")
        .FormulaArray = "=AVERAGE(IF(ROW($P$2:$P$5000)<=X_X_X,IF($B$2:$B$5000=VALUE(S3),IF(ISNUMBER($P$2:$P$5000),$P$2:$P$5000))))"
        .Replace "X_X_X", strFormulaPart1
        .Replace "Y_Y_Y", strFormulaPart2
    End With

Hope this helps!