Excel VBA“范围级自动填充方法失败” [英] Excel VBA "Autofill Method of Range Class Failed"

问题描述

以下VBA代码（Excel 2007）失败，错误1004，范围类自动填充方法失败。任何人都可以告诉我如何解决它？

The following VBA code (Excel 2007) is failing with Error 1004, "Autofill Method of Range Class Failed.". Can anyone tell me how to fix it?

Dim src As Range, out As Range, wks As Worksheet

Set wks = Me
Set out = wks.Range("B:U")
Set src = wks.Range("A6")
src.AutoFill Destination:=out

（注意：我已经有Google等等，它经常出现，我看到的反应与畸变的范围地址有关，AFAIK不是我的问题。

(note: I have Googled, etc. for this. It comes up fairly often, but all of the responses that I saw had to do with malformed range addresses, which AFAIK is not my problem.

根据某人的建议我尝试用以下替换自动填充行：

At someone's suggestion I tried replacing the autofill line with the following:

src.Copy out

这样做会使我的Excel会话成为一个明显的无限循环，消耗100％的CPU，然后永远挂起。

This had the effect of throwing my Excel session into an apparent infinite loop consuming 100% CPU and then just hanging forever.

OK，显然源码必须是自动填充的目标范围的一部分，所以我的代码现在看起来像这样：

OK, apparently the source has to be part of the destination range for autofill. So my code now looks like this:

Dim src As Range, out As Range, wks As Worksheet

Set wks = Me
Set out = wks.Range("B1")
Set src = wks.Range("A6")
src.Copy out

Set out = wks.Range("B:U")
Set src = wks.Range("B1")
src.AutoFill Destination:=out, Type:=xlFillCopy

最后一行出现同样的错误。

Same error on the last line.

推荐答案

从 MSDN ：

目的地必须包含
源范围。

The destination must include the source range.

B：U 不包含 A6 ，因此有错误。我相信你可能想要将 out 设置为 A6：U6 。

B:U does not contain A6 and thus there is an error. I believe that you probably want out to be set to A6:U6.

仅指定列名称意味着您要填充该列中不太可能是所需行为的每一行

Specifiying just the column name means that you want to fill every row in that column which is unlikely to be the desired behvaiour

更新

除了OP的评论下面，更新到原来的答案，这可能是诀窍： p>

Further to the OP's comment below and update to the original answer, this might do the trick:

Dim src As Range, out As Range, wks As Worksheet

Set wks = Me
Set out = wks.Range("B1")
Set src = wks.Range("A6")
src.Copy out

Set out = wks.Range("B1:U1")
Set src = wks.Range("B1")
src.AutoFill Destination:=out, Type:=xlFillCopy

Set out = wks.Range("B:U")
Set src = wks.Range("B1:U1")
src.AutoFill Destination:=out, Type:=xlFillCopy

自动填充一次被约束到一个方向（即水平或垂直）。要从单个单元格填充二维区域，您首先必须沿着该区域的一个边缘自动填充一行，然后在该区域上展开该行。

AutoFill is constrained to a single direction (i.e. horizontal or vertical) at once. To fill a two-dimensional area from a single cell you first have to auto-fill a line along one edge of that area and then stretch that line across the area

对于复制格式化和清除内容的具体情况（凭借源单元格为空），这更好：

For the specific case of copying the formatting and clearing the contents (by virtue of the source cell being empty), this is better:

Dim src As Range, out As Range, wks As Worksheet

Set wks = Sheet1
Set out = wks.Range("B:U")
Set src = wks.Range("A6")
src.Copy out

这篇关于Excel VBA“范围级自动填充方法失败”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！