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

(注意:我为此使用了谷歌等.它经常出现，但我看到的所有响应都与格式错误的范围地址有关，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, 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 评论并更新到原始答案之外，这可能会奏效:

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 一次被限制在一个方向(即水平或垂直).要从单个单元格填充二维区域，您首先必须沿着该区域的一个边缘自动填充一条线，然后在该区域上拉伸该线

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屋！