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
Run-time error: 1004 Range of object '_Worksheet' failed
提问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:
您的代码中有多个错误/问题:
Your statement
wsInput.Range("a12" & i)
certainly does not what you want - it'll return cells A121, A122, ..., A1210000! Instead, trywsInput.Range("A" & (12+i))
orwsInput.Range("A12").Offset(i-1)
. Same problem with the other ranges.in
wsInput.Range("D12" & i, "E12" & i).Copy
you actually copy two cells (D12:E12, after fixing #1)- not sure you want this. If you want this, you could alternatively use theResize
method:wsInput.Range(D12).Offset(i-1).Resize(,2)
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
.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`
您的语句
wsInput.Range("a12" & i)
肯定不是您想要的 - 它会返回单元格 A121、A122、...、A1210000!相反,请尝试wsInput.Range("A" & (12+i))
或wsInput.Range("A12").Offset(i-1)
。其他范围也有同样的问题。在
wsInput.Range("D12" & i, "E12" & i).Copy
你实际上复制了两个单元格(D12:E12,在修复 #1 之后)——不确定你想要这个。如果你想要这个,你也可以使用以下Resize
方法:wsInput.Range(D12).Offset(i-1).Resize(,2)
您没有增加 OutputRowCount,因此每个单元格都将粘贴到 A4(从 #1 修复后,否则到 A41)!添加一行
OutputRowCount=OutputRowCount+1
。您可以简单地分配以下内容,而不是复制和粘贴
.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 .SpecialCells
and Intersect
, i.e. you could your whole For loop with
最后但并非最不重要的是,不要循环遍历每个单元格,而是考虑使用.SpecialCells
and 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.
现在它工作正常。