vba 运行时错误 1004。对象 '_Worksheet' 的方法 'Range' 失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18011076/
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. Method 'Range' of object '_Worksheet' failed
提问by Kazuo
I have the following 2000 lines of code that get's a compile error because the procedure is overloaded:
我有以下 2000 行代码，因为该过程已重载，因此出现编译错误：
Sheet7.Range("H2").Value = Sheet7.Range("H2").Value + Sheet7.Range("K2").Value
...
Sheet7.Range("H2001").Value = Sheet7.Range("H2001").Value + Sheet7.Range("K2001").Value
If I have, say, up to ten lines, it works. But I need the code to work for 2000 lines and that's when I get the compile error. To address the compile error, I used the following loop, but it gives me Run-time error 1004. Method 'Range' of object '_Worksheet' failed.
如果我有多达十行，它就可以工作。但是我需要代码可以运行 2000 行，这就是我收到编译错误的时候。为了解决编译错误，我使用了以下循环，但它给了我运行时错误 1004。对象 '_Worksheet' 的方法 'Range' 失败。
Dim RowNo as Integer
For RowNo = 2 to 2001 Step 1
Range(Cells(RowNo, 8)).Value = Range(Cells(RowNo, 8)).Value + Range(Cells(RowNo, 11)).Value
Next RowNo
How do I make the loop work without getting a run-time error 1004?
如何使循环正常工作而不会出现运行时错误 1004？
回答by Kazimierz Jawor
Change the line inside you loop into:
将循环内的行更改为：
a) with full sheet references
a) 有完整的工作表参考
Sheet7.Cells(RowNo, 8).Value = Sheet7.Cells(RowNo, 8).Value + Sheet7.Cells(RowNo, 11).Value
b) or shorter, without sheet references:
b) 或更短，没有工作表参考：
Cells(RowNo, 8).Value = Cells(RowNo, 8).Value + Cells(RowNo, 11).Value
回答by dee
Another possibility would be to use array-formula.
另一种可能性是使用array-formula。
So you do not have to loop through all cells but instead let Excel to calculate it and return array with results. Code executes quicker than for-each loop. HTH
因此，您不必遍历所有单元格，而是让 Excel 计算它并返回带有结果的数组。代码执行速度比 for-each 循环快。HTH
Public Sub test()
Dim range1 As Range
Set range1 = Range("H2:H2001")
Dim range2 As Range
Set range2 = Range("K2:K2001")
Dim result
Dim myFormula As String
myFormula = "={0}+{1}"
Application.ReferenceStyle = xlR1C1
myFormula = VBA.Strings.Replace(myFormula, "{0}", range1.Address(ReferenceStyle:=xlR1C1))
myFormula = VBA.Strings.Replace(myFormula, "{1}", range2.Address(ReferenceStyle:=xlR1C1))
result = Application.Evaluate(myFormula)
range1.Value = result
Application.ReferenceStyle = xlA1
End Sub
回答by sous2817
You could also do this with a helper column:
您也可以使用辅助列执行此操作：
Sub test()
Application.ScreenUpdating = False
With Sheet7
.Range("I2:I2001").Formula = "=H2+K2"
.Range("H2:H2001").Value = .Range("I2:I2001").Value
.Range("I2:I2001").ClearContents
End With
Application.ScreenUpdating = True
End Sub
Ideally, you wouldn't want to hard code in your row references if it could be avoided. I also arbitrarily picked column I to be the helper column, you should adjust it to suit your sheet layout.
理想情况下，如果可以避免的话，您不希望在行引用中进行硬编码。我还随意选择了 I 列作为辅助列，您应该对其进行调整以适合您的工作表布局。
回答by azar
eror Method 'Range' of object '_worksheet' failed . . . .
对象“_worksheet”的错误方法“范围”失败。. . .
Sub kar()
Dim i, j
For i = 8 To 15
For j = 9 To 15
If (Range("ji") = Range("jj") And Range("gi") = "????" And Range("gj") = "????" And (Abs(Range("ki") - Range("kj") <= 50))) Then
Range("ui") = -(Range("ki") - Range("kj")) * hi
Range("uj") = -(Range("ki") - Range("kj")) * hj
End If
If (Range("ji") = Range("jj") And Range("gi") = "????" And Range("gj") = "????" And (Abs(Range("ki") - Range("kj")) <= 50)) Then
Range("uj").Value = -(Range("ki") - Range("kj")) * hi
Range("ui").Value = -(Range("ki") - Range("kj")) * hj
End If
Next j
Next i
End Sub
