excel - 如何为同一变量分配更多范围？

Question

我想为变量分配更多范围。

在线显示消息“运行时错误：无效的过程调用或参数” Set bckup = Union(.Cells(3, i), bckup)。我尝试使用功能“范围”

设置 bckup = Range(.Cells(3, i), bckup)

但也收到消息“对象_global的方法范围失败”或 Set bckup = Range(.Cells(3, i) & bckup) “对象变量或未设置块变量”

With Workbooks("Deactivate.xlsm").Worksheets(2)
For i = 500 To 2 Step -1

    If .Cells(3, i).Value <> "" Then

        If .Cells(7, i) = 0 Then

        'Run time error :invalid procedure call or argument
        Set bckup = Union(.Cells(3, i), bckup)

        bckup.Select
        End If

    End If
Next i
End With

我想将所有符合条件的范围存储在一个变量中。

标签： excelvba

Answer 1

您需要首先检查是否bckup包含无法与Nothing.

With Workbooks("Deactivate.xlsm").Worksheets(2)
    For i = 500 To 2 Step -1
        If .Cells(3, i).Value <> "" Then
            If .Cells(7, i) = 0 Then
                If bckup Is Nothing Then
                    Set bckup = .Cells(3, i)
                Else
                    Set bckup = Union(.Cells(3, i), bckup)
                End If
            End If
        End If
    Next i
End With