excel - 使用数组循环将格式应用于范围 - VBA

Question

使用 VBA 我可以:

设置两个范围

创建一个包含所有范围名称的数组

循环数组以将格式应用于数组中的每个范围

但是在线 .Interior.Color = vbRed
我收到以下错误:

Run-time error '424': Object required

代码

Sub test()

    Dim rng1 As Range, rng2 As Range
    Dim strRanges As Variant
    Dim i As Long

    Set rng1 = Sheet1.Range("A1:D1")
    Set rng2 = Sheet2.Range("C5:H5")

    strRanges = Split("rng1,rng2", ",")

    For i = LBound(strRanges) To UBound(strRanges)
        With strRanges(i)
            .Interior.Color = vbRed
        End With
    Next i

End Sub

我已经使用:
With Range(strRanges(i))
代替:
With strRanges(i)
没有任何运气!

任何帮助将不胜感激。

Answer 1

如果范围在同一个工作表中，您可以在一行中执行此操作

Sheet1.Range("A1:D1,C5:H5").Interior.Color = vbRed

如果范围在同一个工作表中，您可以使用联合

Dim rng1 As Range, rng2 As Range
Set rng1 = Sheet1.Range("A1:D1")
Set rng2 = Sheet1.Range("C5:H5")
Union(rng1, rng2).Interior.Color = vbRed

如果它们位于不同的工作表中，您可以将真实数组用于您的范围

Sub test()    
    Dim rng(1 To 2) As Range
    Set rng(1) = Sheet1.Range("A1:D1")
    Set rng(2) = Sheet2.Range("C5:H5")

    Dim i As Long
    For i = LBound(rng) To UBound(rng)
        With rng(i)
            .Interior.Color = vbRed
        End With
    Next i    
End Sub

如果您没有编号的范围变量名称，那么您可以使用另一个数组:

Sub test()    
    Dim rngABC As Range, rngXYZ As Range
    Set rngABC = Sheet1.Range("A1:D1")
    Set rngXYZ = Sheet1.Range("C5:H5")

    Dim ArrRng() As Variant
    ArrRng = Array(rngABC, rngXYZ)

    Dim rng As Variant
    For Each rng In ArrRng
        rng.Interior.Color = vbRed
    Next rng     
End Sub

请注意，如果您认为必须使用编号变量名称，例如

Dim rng1 As Range, rng2 As Range

这始终是使用数组的明显标志:

Dim rng(1 To 2) As Range

编号的变量名称是一种不好的做法。始终选择有意义的名称。