vba - 对于每个编译错误(多个范围)

Question

全部，

我敢肯定有一个快速的答案，但是这让我沮丧了一个小时，所以如果这个问题非常基本，我深表歉意。

我有一个 For Each 循环，我需要迭代几次，但是当我输入两个以上的范围来迭代时，屏幕截图会显示错误。

    Dim TargetCell As Range, ICol As Integer
    Set TargetCell = Rows("9").Find(What:="Forecast", LookIn:=xlValues, LookAt:=xlPart)
    If Not TargetCell Is Nothing Then ICol = TargetCell.Column



For Each c In Range(Cells(12, ICol), Cells(18, 73), Cells(19, ICol), Cells(42, 73), Cells(47, ICol), Cells(53, 73), Cells(55, ICol), Cells(76, 73))

任何有关错误的帮助将不胜感激

编译错误信息:


*****更新

我已经更新了我的代码以拥有工会，这已经解决了论坛帮助的问题
请参阅下面的新代码；

Dim Rng1, Rng2, Rng3, Rng4 As Range

Set Rng1 = Range(Cells(12, ICol), Cells(18, 73))
Set Rng2 = Range(Cells(19, ICol), Cells(42, 73))
Set Rng3 = Range(Cells(47, ICol), Cells(53, 73))
Set Rng4 = Range(Cells(55, ICol), Cells(76, 73))

 Dim C as variant
    For Each c In Union(Rng1, Rng2, Rng3, Rng4)

发布答案以供将来引用

Answer 1

有很多方法可以制作单元格循环。如评论中所述，Range.Union真是个好主意。另一种是将所有单元格放在一个数组中并像这样循环遍历它们:

Option Explicit

Public Sub TestMe()

    Dim c As Variant
    Dim Icol As Long:    Icol = 5

    For Each c In Array(Cells(12, Icol), Cells(18, 73), Cells(19, Icol), Cells(42, 73))
        Debug.Print c.Address
        c.Interior.Color = vbRed
    Next c

End Sub