Excel自动填充问题 [英] Excel AutoFill problem

问题描述

我在使用VBA脚本时遇到问题.该函数用一些公式填充工作表.在大多数情况下，它都能正常工作，但是当我在更大的数据集上运行时，它崩溃了:

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:

运行时错误'1004':Range类的自动填充方法失败

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

调试器指向上一个自动填充调用.当我查看工作表时，似乎AutoFill调用在大约3/4的途中有效，但是停止了.

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

有什么建议吗?

我发现了问题: http://support.microsoft.com/kb/166342

在Excel 2003和早期版本的Excel中，一个工作表最多可以包含65,472个引用另一个工作表的数组公式.如果要使用更多的公式，请将数据拆分为多个工作表，以减少数据量超过65,472个对单个工作表的引用."

"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. "

推荐答案

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

在Excel 2003和早期版本的Excel中，一个工作表最多可以包含65,472个引用另一个工作表的数组公式.如果要使用更多的公式，请将数据拆分为多个工作表，以减少数据量超过65,472个对单个工作表的引用."

"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自动填充问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！