Excel Autofill Problem

[mdn0420]

Hi, 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.

Any suggestions?

    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

 

[mdn0420]

it stops around row 1500 on the worksheet... if that helps

 

[Norie]

Don't use AutoFill.

Sorry for not giving an exact solution - the Winter Olympics just started.

 

[mdn0420]

What would be the best workaround for AutoFill... and what exactly is wrong with AutoFill? (other than the fact that it seems to be breaking in this case)

 

[mdn0420]

anyone?

 

[mdn0420]

.