如何修复运行时错误 1004 方法对象全局范围失败

Question

嗨，我正在尝试运行此代码，并且它以前在其他项目中工作过，所以我不明白为什么它不适用于此代码。

我试过查看我是否错过了以前代码中的内容，但我认为我没有

Sub DepolPotential()
DataSheet.Activate
Dim DepolPotential As Range
Dim Count1 As Range
Dim Cells1 As Range
Dim Count2 As Range
Set Cells1 = Range("N2", Range("N2").End(xlDown))
Set Cells2 = Range("N2", Range("N2").End(xlDown))
Set Count2 = Range("N2")
Do
Set Cells1 = Range(Count2, Range("N2").End(xlDown))
Set Count1 = Count2
For Each Count1 In Cells1
If Count2 Is Nothing Then
Exit Do
End If
If Count1.Value < 1 And Count1.Offset(3, 0).Value < 1 Then
Set DepolPotential = Count1.Offset(0, -12)
Count1.Offset(0, 20).Value = DepolPotential.Value
Exit For
End If
Next Count1
Dim InstOn As Range
If Count1 Is Nothing Then
Exit Do
End If
Set Cells2 = Range(Count1, Range("N2").End(xlDown))
For Each Count2 In Cells2
If Count2.Value > 1 And Count1.Offset(3, 0).Value > 1 Then
Set InstOn = Count2.Offset(0, -12)
Count2.Offset(0, 21).Value = InstOn.Value
Count2.Offset(1, 22).Value = InstOn.Offset(1, 0).Value
Set Count2 = Count2.Offset(2, 0)
Exit For
End If
Next Count2
Loop Until Count1 Is Nothing

我在线收到错误

Set Cells1 = Range(Count2, Range("N2").End(xlDown)) 

我不确定为什么我在以前的代码中收到此错误，这没有给我错误。

Answer 1

我不知道您的数据是什么样子的，但是外部循环在第二次迭代时中断，并带有空DataSheet，并出现您描述的错误。这是一件好事，因为cells1.Address$N$2:$N$1048576一张空纸上......但这并不重要，因为第一个空单元格满足条件并退出第一个内部循环。

但这只是一个症状，而不是真正的问题。

第二个循环迭代的范围与第一个循环完全相同，但这次没有一个空单元格满足条件，循环继续迭代工作表上的每一个痛苦行。

当循环退出时，count2循环变量引用Nothing- 这就是第二次迭代爆炸的原因：[_Global|Worksheet].Range接受几种不同的方法来指定单元格范围，但给出它Nothing是一个非法的参数;它引发错误，执行突然停止。

发生了什么事情？VBA 语言规范与此处相关：

当<for-each-statement>完成执行后，<bound-variable-expression>的值是<collection>中最后一个元素的数据值。

^{_{https://learn.microsoft.com/en-us/openspecs/microsoft_general_purpose_programming_languages/ms-vbal/b132463a-fd25-4143-8fc7-a443930e0651}}

似乎根据规范，当循环退出时，count1和count2都应该有一个有效的对象引用。但是，似乎Microsoft对VBA语言规范的实现的工作方式不同。下面是一个最小的重现示例：

Public Sub Test()
Dim c As Collection
Set c = New Collection
c.Add New Collection 'any object will do
Dim o As Object
For Each o In c
'Exit For
Next
Debug.Print o Is Nothing
End Sub

运行此代码一次，注释掉Exit For，然后取消注释语句并再次运行它。如果循环运行完成，则迭代对象集合的For Each循环的"绑定变量"将被Nothing。

这意味着如果第一个内部循环运行完成，您将在此处遇到相同的错误：

Set Cells2 = Range(Count1, Range("N2").End(xlDown))

因为那时Count1会Nothing。在一张空纸上，Count1此时指向$N$2，所以Cells2$N$2:$N$1048576.

当第二个内部循环运行到完成时，Count2Nothing，并且由于循环条件只查看Count1...

Loop Until Count1 Is Nothing

......当Count2作为参数传递给_Global.Range时，外循环的第二次迭代会爆炸，失败并出现错误 1004：

Set Cells1 = Range(Count2, Range("N2").End(xlDown))

我想创可贴解决方案可能是在使用前验证是否Count2 Is Nothing，尽管这与更改退出条件以检查Count1和Count2完全相同：

Loop Until Count1 Is Nothing Or Count2 Is Nothing

。我不认为这是正确的解决方案。事实上，我不确定这是一个解决方案。

我不能 100% 确定，因为我没有花太多时间试图弄清楚Count2不Nothing如何影响外循环(而且我没有任何数据可以玩)，但我认为这可能恰好在做同样的事情：

Dim interestingCells As Range
Set interestingCells = DataSheet.Range("N2:N" & DataSheet.Rows.Count).End(xlUp)
Dim cell As Range
For Each cell In interestingCells
If cell.Value < 1 And cell.Offset(3).Value < 1 Then
cell.Offset(0, 20).Value = cell.Offset(0, -12).Value
ElseIf cell.Value > 1 And cell.Offset(3).Value > 1 Then
cell.Offset(0, 21).Value = cell.Offset(0, -12).Value
cell.Offset(1, 22).Value = cell.Offset(1).Value
End If
Next

老实说，我认为真正的解决方案是退后一步，重新评估这个循环到底意味着什么，废弃旧代码，从头开始重写它。其他任何事情都会增加已经难以理解的代码段的复杂性。除了回收的For Each循环变量之外，行偏移量特别令人困惑：详细说明为什么下一行需要受到影响的注释是有序的 - 请注意，这种逻辑可能需要以特定方式对数据进行排序......这可能是一个糟糕的假设，即一个灾难性的错误等待显示其丑陋的头。另请注意，如果迭代范围恰好跨越整个列，则当cell位于最后一行时，对于任何正值n都将失败，如果它靠近工作表的最后一行，则对于大于其下剩余行数的任何大于n行数的值，cell.Offset(n)都将失败。

另请注意获取包含数据的最后一行/单元格的.End(xlUp)逻辑：这样，空工作表将不会运行一次迭代。

最后，请注意显式限定的Range调用，以便它解析为DataSheet.Range而不是_Global.Range- 这是我看到_Global.Range抛出的错误 1004 的极少数几次之一，问题的根源不是使用不合格的Range调用隐式引用任何ActiveSheet是什么。

Answer 2

启动了您的代码。 在线

Set Cells1 = Range (Count2, Range ('N2'). End (xlDown))

计数2变成了虚无

作为解决方案，移动代码

If Count2 Is Nothing Then
Exit do
End if

行前

Set Cells1 = Range (Count2, Range ('N2'). End (xlDown))