对象'_global'的Excel VBA方法'范围'失败错误1004 [英] Excel VBA Method 'Range' of object'_global' failed error 1004

问题描述

我不知道我的生活中为什么我得到了我的做法对象的范围全球失败了。就像名称为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 
 
与应用程序
 
'.ScreenUpdating = False'不要垃圾邮件接口与选择
 
与ActiveSheet 
 
 LastRow = .Cells（。 Rows.Count，A）。End（xlUp）.Row'将LastRow中的最后一行的编号
 
 End with 
 
 Set Allrws = Range（A2 ：S& CStr（LastRow））'设置范围要被观察
 
对于每个Rws在Allrws.Rows中，对于Allrws范围内的每一行，将其称为Rws并执行以下操作
 
设置RC2 = Rws.Offset（0，3）.Resize（1，1）'移动并调整RC2的范围，以仅包括列D下的单元格
设置RC3 = Rws。 Offset（0，7）。Resize（1，1）'移动并调整RC3的范围到include e只有列下的单元格
 I = 0'初始化行删除的计数器
 
 Do While（Range（RC3）.Value& 而且我30）'只要RC指向一个空的单元格，并且我们没有删除超过30行继续删除行
 
 If（range（RC2）.Value =Permit& Design或range （RC2）.Value =Miscellanious）然后'不要删除行，如果Permit&设计或错误的是在列D下的单元格
 
 I = 30'如果为true，则退出循环
 
 Else 
 
 Selection.EntireRow.Delete'删除所选行如果为false 
 
 I = I + 1'添加到计数器
 
结束如果
 
循环回到起始的执行
 
下一个Rws'返回For Each并将下一行放在Rws 
 
 .ScreenUpdating = True'现在更新界面与更改
 
结束
 
 end sub 
  

解决方案

  Do While（Range（RC3）.Value<>And I< 30）
  pre> 
 
 
   RC3 是一个范围对象。  Range（SomeRangeObject）确实是 Range（SomeRangeObject.Value），所以除非 RC3 .Value 包含有效的范围地址字符串，不合格 范围调用将会爆炸。
 
 
 
注意不合格：您的代码隐式适用于 ActiveSheet ：
  Set Allrws = Range（A2：S& CStr（LastRow））
  
无论何时使用 Range ，它隐含地执行 ActiveSheet。范围，通过 _Global 隐藏模块。
 
 
 
不合格 ，单元格，行，列和名称调用都隐含地指代 ActiveSheet ，对这一事实的误解是背后的原因侧栏中的每一个相关问题（我检查的那个），在thi上还有数千个的网站：这是一个非常的常见错误来源。因此，限定工作表成员调用并避免出现问题。
 
 
 
您的代码发生正常工作修改）。如果使用ActiveSheet 块的更改为使用Sheet12 ，您将会看到源自所有不合格的范围呼叫。
 
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.
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

解决方案
Do While (Range(RC3).Value <> "" And I < 30)
RC3 is a Range object. What Range(SomeRangeObject) does is really Range(SomeRangeObject.Value), so unless RC3.Value contains a valid range address string, that unqualified Range call is going to blow up.

Note unqualified: your code implicitly works off the ActiveSheet:
Set Allrws = Range("A2:S" & CStr(LastRow))
Whenever Range is used like this, it's implicitly doing ActiveSheet.Range, through the _Global hidden module.

Unqualified Range, Cells, Rows, Columns and Names calls 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 extremely common source of bugs. So, qualify worksheet member calls and avoid problems.

Your code happens to work (well, given the above modification). If the With ActiveSheet block was changed to With Sheet12, you would start seeing issues stemming from all the unqualified Range calls.

                        
这篇关于对象'_global'的Excel VBA方法'范围'失败错误1004的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！