vba 对象'_global'的Excel VBA方法'Range'失败错误1004
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43901895/
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
Excel VBA Method 'Range' of object'_global' failed error 1004
提问by Austin Huddleson
I can't for the life of my figure out why I'm getting "'Range' of object'_global' failed" on my do while statement. It's like the range named RC3 isn't being recognized. Any help is greatly appreciated.
我一生都无法弄清楚为什么我的 do while 语句会出现“对象‘_global’的‘范围’失败”。这就像名为 RC3 的范围未被识别。任何帮助是极大的赞赏。
Sub DeleteBlankRows()
Dim Allrws As Range
Dim Rws As Range
Dim RC2 As Range
Dim RC3 As Range
Dim I As Integer
Dim CopyRange As Range
Dim LastRow As Long
With Application
'.ScreenUpdating = False ' don't spam the interface with selections
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row ' Put the number of the last row in LastRow
End With
Set Allrws = Range("A2:S" & CStr(LastRow)) ' set range to be observed
For Each Rws In Allrws.Rows ' for each row in the range of Allrws call it Rws and do the following with it
Set RC2 = Rws.Offset(0, 3).Resize(1, 1) ' move and resize the range of RC2 to include only the cell under column D
Set RC3 = Rws.Offset(0, 7).Resize(1, 1) ' move and resize the range of RC3 to include only the cell under column I
I = 0 ' initilize the rows deleted counter
Do While (Range(RC3).Value <> "" And I < 30) ' as long as RC points to a empty cell and we haven't removed more then 30 rows keep removing rows
If (range(RC2).Value = "Permit & Design" Or range(RC2).Value = "Miscellanious") Then 'don't delete row if Permit & Design or Miscellanious is in the cell under column D
I = 30 ' escape the loop if true
Else
Selection.EntireRow.Delete ' delete the selected row if false
I = I + 1 ' add 1 to the counter
End If
Loop ' Go back to the start of the Do While
Next Rws ' go back to the For Each and put the next row in Rws
.ScreenUpdating = True ' now update the interface with the changes
end with
end sub
回答by Mathieu Guindon
Do While (Range(RC3).Value <> "" And I < 30)
RC3is a Rangeobject. What Range(SomeRangeObject)does is really Range(SomeRangeObject.Value), so unless RC3.Valuecontains a valid range address string, that unqualifiedRangecall is going to blow up.
RC3是一个Range对象。什么Range(SomeRangeObject)是真的Range(SomeRangeObject.Value)，所以除非RC3.Value包含一个有效的范围地址字符串，否则那个不合格的Range调用将会爆炸。
Note unqualified: your code implicitlyworks off the ActiveSheet:
注意不合格：您的代码隐式地工作于ActiveSheet：
Set Allrws = Range("A2:S" & CStr(LastRow))
Whenever Rangeis used like this, it's implicitly doing ActiveSheet.Range, through the _Globalhidden module.
每当Range像这样使用时，它都是ActiveSheet.Range通过_Global隐藏模块隐式执行的。
Unqualified Range, Cells, Rows, Columnsand Namescalls are all implicitly referring to the ActiveSheet, and the misunderstanding of this fact is the reason behind every single "Related" question in the side bar (the ones I checked anyway), and there are thousands more of the same on this site: it's an extremelycommon source of bugs. So, qualify worksheet member callsand avoid problems.
不合格的Range，Cells，Rows，Columns和Names电话都隐含指ActiveSheet，和这一事实的误解是落后于侧栏的每一个“相关”的问题（那些我检查反正）的原因，也有数以千计的相同的上这个站点：它是一个极其常见的错误来源。因此，限定工作表成员调用并避免出现问题。
Your code happensto work (well, given the above modification). If the With ActiveSheetblock was changed to With Sheet12, you would start seeing issues stemming from all the unqualified Rangecalls.
您的代码恰好可以工作（好吧，鉴于上述修改）。如果该With ActiveSheet块更改为With Sheet12，您将开始看到所有不合格Range调用所产生的问题。
