Excel AutoFill problem

Question

I'm having problems with a VBA script. The function fills in a sheet with some formulas. It works fine most of the time, however when I run it on a larger data set... it crashes with:

Run-time error '1004': AutoFill method of Range class failed

The debugger points to the last AutoFill call. When I look at the sheet, it seems that the AutoFill call worked about 3/4 of the way but stops.

With Sheets("OPENS")
    ' Fill OPENS formulas
    .Range("B" & firstRowOPENS - 3) = "=DATE(YEAR($B$1), MONTH($B$1)+2, DAY($B$1))"
    .Range("B" & firstRowOPENS - 3).NumberFormat = "[$-409]mmm-yy;@"
    .Range("C" & firstRowOPENS - 3) = "=C1+2"

    .Range("B" & firstRowOPENS - 1) = "=SUBTOTAL(109,B" & firstRowOPENS & ":B" & lastRowOPENS & ")"
    .Range("B" & firstRowOPENS - 1).AutoFill .Range("B" & firstRowOPENS - 1 & ":" & lastColOPENS & firstRowOPENS - 1), xlFillValues

    .Range("B" & firstRowOPENS) = "=SUM(C" & firstRowOPENS & ":" & lastColOPENS & firstRowOPENS & ")"
    .Range("C" & firstRowOPENS).FormulaArray = "=SUM( ('Combined Input'!R2C2:R" & lastRowCI & "C2=RC1) * ('Combined Input'!R2C45:R" & lastRowCI & "C45=R2C) * ('Combined Input'!R2C43:R" & lastRowCI & "C43 = R1C4) * INDEX('Combined Input'!R2C5:R" & lastRowCI & "C40, 0,R" & firstRowOPENS - 3 & "C3,1)) * R[-" & (lastRowOPENS - firstRowOPENS + 5) * 2 & "]C" & OPENS_PROB_COL
    .Range("C" & firstRowOPENS).AutoFill .Range("C" & firstRowOPENS & ":" & lastColOPENS & firstRowOPENS), xlFillValues

    If lastRowOPENS > firstRowOPENS Then
        .Range("B" & firstRowOPENS & ":" & lastColOPENS & firstRowOPENS).AutoFill .Range("B" & firstRowOPENS & ":" & lastColOPENS & lastRowOPENS), xlFillValues
    End If

    setBorders .Range("A" & firstRowOPENS - 2 & ":" & lastColOPENS & lastRowOPENS)
End With

Any suggestions?

Edit:

I found the problem: http://support.microsoft.com/kb/166342

"In Excel 2003 and in earlier versions of Excel, a single worksheet may contain a maximum of 65,472 array formulas that refer to another worksheet. If you want to use more formulas, split the data into multiple worksheets so that there are fewer than 65,472 references to a single worksheet. "

It seems to be getting stuck at line 1500 on my sheet. Even when I try to manually AutoFill... it doesn't work — lucks
it is calculated before this block. it seems to have something to do with the FormulaArray definition. — lucks
auto fill doesn't work if it is more than just one column. could that be the problem? — guitarthrower
autofill does work with more than 1 column. it works in other sections of the sheet, it just fails party way for some reason. i think it has something to do with the formula array... since i can't manually copy the formula into the cells where it fails either — lucks

lucks lucks · Accepted Answer · 2010-06-18T21:36:28

http://support.microsoft.com/kb/166342

"In Excel 2003 and in earlier versions of Excel, a single worksheet may contain a maximum of 65,472 array formulas that refer to another worksheet. If you want to use more formulas, split the data into multiple worksheets so that there are fewer than 65,472 references to a single worksheet. "

Excel AutoFill problem

1 Answers