vba 运行时错误:1004 对象“_Worksheet”的范围失败

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/16927378/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 21:27:33  来源:igfitidea点击:

Run-time error: 1004 Range of object '_Worksheet' failed

excelvba

提问by user2437807

Okay here is my code, I'm pretty sure the error is coming from something silly in the way stuff is named. I'm just starting to learn VBA so totally noob at this and can't catch what's wrong. Any input would be appreciated.

好的,这是我的代码,我很确定该错误来自一些愚蠢的东西命名方式。我刚刚开始学习 VBA,所以完全是菜鸟,无法发现哪里出了问题。任何输入将不胜感激。

Sub test()

Dim wsInput As Worksheet: Set wsInput = ActiveSheet
Dim wsOutput As Worksheet: Set wsOutput = Workbooks.Open("C:\output.xls").Sheets(1)
Dim OutputRowCount As Integer: OutputRowCount = 1

    For i = 1 To 10000
     If wsInput.Range("a12" & i) <> "" Then
         wsInput.Range("D12" & i, "E12" & i).Copy

       wsOutput.Range("A4" & OutputRowCount).PasteSpecial Paste:=xlPasteValues
    End If
    Next

End Sub

回答by Peter Albert

There's multiple errors/problems in your code:

您的代码中有多个错误/问题:

  1. Your statement wsInput.Range("a12" & i)certainly does not what you want - it'll return cells A121, A122, ..., A1210000! Instead, try wsInput.Range("A" & (12+i))or wsInput.Range("A12").Offset(i-1). Same problem with the other ranges.

  2. in wsInput.Range("D12" & i, "E12" & i).Copyyou actually copy two cells (D12:E12, after fixing #1)- not sure you want this. If you want this, you could alternatively use the Resizemethod: wsInput.Range(D12).Offset(i-1).Resize(,2)

  3. You do not increase OutputRowCount, therefore every cell will be pasted to A4 (after fix from #1, else to A41)! Add a line OutputRowCount=OutputRowCount+1.

  4. Instead of copying and pasting, you could simply assign the .Value: wsOutputRange("A"& 4 + OutputRowCount).Resize(,2).Value = Input.Range(D12).Offset(i-1).Resize(,2).Value`

  1. 您的语句wsInput.Range("a12" & i)肯定不是您想要的 - 它会返回单元格 A121、A122、...、A1210000!相反,请尝试wsInput.Range("A" & (12+i))wsInput.Range("A12").Offset(i-1)。其他范围也有同样的问题。

  2. wsInput.Range("D12" & i, "E12" & i).Copy你实际上复制了两个单元格(D12:E12,在修复 #1 之后)——不确定你想要这个。如果你想要这个,你也可以使用以下Resize方法:wsInput.Range(D12).Offset(i-1).Resize(,2)

  3. 您没有增加 OutputRowCount,因此每个单元格都将粘贴到 A4(从 #1 修复后,否则到 A41)!添加一行OutputRowCount=OutputRowCount+1

  4. 您可以简单地分配以下内容,而不是复制和粘贴.Value: wsOutputRange("A"& 4 + OutputRowCount).Resize(,2).Value = Input.Range(D12).Offset(i-1).Resize(,2) .价值`

Last but not least, instead of looping over each cell, consider using .SpecialCellsand Intersect, i.e. you could your whole For loop with

最后但并非最不重要的是,不要循环遍历每个单元格,而是考虑使用.SpecialCellsand Intersect,即您可以使用整个 For 循环

Application.Union( _
    wsInput.Range("A4").Resize(10000).SpecialCells(xlCellTypeFormulas),
    wsInput.Range("A4").Resize(10000).SpecialCells(xlCellTypeValues)) _
    .Offset(,3).Resize(,2).Copy
wsOutput.Range("A4").PasteSpecial(xlPasteValues)

Hope that helps!

希望有帮助!

回答by JMK

The maximum amount of rows you can have in Excel 32-bit is 1048576, but the last row you are trying to access here is 1210000. The below code works (all I have done is changed 10000 to 9999), but as Peter says, this probably isn't what you really want to do, unless you have some bizarre business reason or something:

您在 Excel 32 位中可以拥有的最大行数是 1048576,但您在这里尝试访问的最后一行是 1210000。下面的代码有效(我所做的只是将 10000 更改为 9999),但正如 Peter 所说,这可能不是您真正想要做的,除非您有一些奇怪的商业原因或其他原因:

Sub test()

Dim wsInput As Worksheet: Set wsInput = ActiveSheet
Dim wsOutput As Worksheet: Set wsOutput = Workbooks.Open("C:\output.xls").Sheets(1)
Dim OutputRowCount As Integer: OutputRowCount = 1

    For i = 1 To 9999
     If wsInput.Range("a12" & i) <> "" Then
         wsInput.Range("D12" & i, "E12" & i).Copy

       wsOutput.Range("A4" & OutputRowCount).PasteSpecial Paste:=xlPasteValues
    End If
    Next

End Sub

回答by Praveen Kumar V

Error: Method 'Paste' of object '_Worksheet' failed - 1004

错误:对象“_Worksheet”的方法“粘贴”失败 - 1004

Solution: Need to remeber the problems in Excel before copy the shapes from one sheet to another sheet. 1. Activate the Sheet(from where you are copying). 2. Select the Shapes from Sheet. 3. Copy the shapes from the Sheet. 4. Paste to shape to target sheet

解决方案:在将形状从一张纸复制到另一张纸之前,需要记住 Excel 中的问题。1. 激活工作表(从您复制的位置)。2. 从图纸中选择形状。3. 从工作表复制形状。4.粘贴到目标表的形状

Example: Previously my code is like below:

示例:以前我的代码如下所示:

       Sheet1.Shapes(0).Copy
       Targetsheet.Paste

I have modified the like below:

我已经修改如下:

       Sheet1.Activite
       Sheet1.Shapes(0).Select
       Sheet1.Shapes(0).Copy
       Targetsheet.Paste

Now it is working fine.

现在它工作正常。