vba 运行时错误 1004“范围类的方法失败”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15912598/
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
Runtime error 1004 "Method of Range Class Failed"
提问by user2263642
I keep running into an error (which happens with no pattern or percieved reason). The error occurs when I try to do a pastespecial (formulas or values). I am curious if there is an alternative way to paste values or formulas in VBA given that the number of rows in the array is variable (1 to ~100). I have attached the snippet of code below.
我一直遇到错误（这种情况没有模式或感知到的原因）。当我尝试执行 pastespecial（公式或值）时发生错误。鉴于数组中的行数是可变的（1 到 ~100），我很好奇是否有另一种方法可以在 VBA 中粘贴值或公式。我附上了下面的代码片段。
'both length and selectionarea are defined as strings
'长度和选择区域都定义为字符串
Range("P1").Select
'length is a formula in a cell that counts the length of the table
'length 是单元格中计算表格长度的公式
length = ActiveCell.Value
selectionarea = "B3:CM" + length
Range("B2:CM2").Select
Selection.Copy
Range(selectionarea).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
selectionarea = "C2:O" + length
Range(selectionarea).Select
Selection.Copy
Range("BL3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
回答by Tim Williams
Try something like this
尝试这样的事情
Dim rng As Range, Length
Length = Range("P1").Value
'EDIT use this for formulas
Range("B2:E" & Length).Formula = Range("B2:E2").Formula
'or this should also work
'Range("B2:E" & Length).FillDown
Set rng = Range("C2:O" & Length)
Range("BL3").Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
回答by Praveen Kumar V
Error: Method 'Paste' of object '_Worksheet' failed - 1004
Solution: Need to remeber the problems in Excel before copy the shapes from one sheet to another sheet.
错误：对象“_Worksheet”的“粘贴”方法失败 - 1004
解决方案：在将形状从一张纸复制到另一张纸之前，需要记住Excel 中的问题。
- Activate the Sheet(from where you are copying).
- Select the Shapes from Sheet.
- Copy the shapes from the Sheet.
- Paste to shape to target sheet
- 激活工作表（从您复制的位置）。
- 从工作表中选择形状。
- 从工作表复制形状。
- 粘贴到目标表的形状
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.
现在它工作正常。
