循环和If语句。如何在一条语句中组合

Question

我的项目有以下for循环：

'Range and Range Array variables
Dim rngArray As Object
Dim rngArrayMain(0 To 9) As Excel.Range

rngArrayMain(0) = xlWSEE.Range("I40")
rngArrayMain(3) = xlWSEE.Range("V46")
rngArrayMain(4) = xlWSEE.Range("L48:N48")
rngArrayMain(5) = xlWSEE.Range("L51")
rngArrayMain(6) = xlWSEE.Range("J35")
rngArrayMain(7) = xlWSEE.Range("J53")
rngArrayMain(1) = xlWSEE.Range("B57:B61")
rngArrayMain(2) = xlWSEE.Range("B70")
rngArrayMain(8) = xlWSEE.Range("L47")
rngArrayMain(9) = xlWSEE.Range("O47")

For Each rngArray In rngArrayMain

    With rngArray

        .Font.Bold = True
        .Font.Size = 10
        .Font.Name = "Calibri"
        .Font.ColorIndex = 2 'white

    End With

Next rngArray

没问题，效果很好。但是，我必须分离数组的最后两个元素，因为它们的Font.ColorIndex = 1。

因此，基本上我的for循环应该是:如果数组0-7执行此操作

如果数组8-9，则执行此操作。

如何修改代码以反映此更改？如果这个问题看起来很基本的话，那是因为我编程时间不长，我所知道的是自学的(谷歌，必应，还有一些书)。

谢谢。

Answer 1

使用For循环而不是For Each，如下所示：

For i As Integer = 0 To rngArrayMain.Length - 1
    ' First seven items do this (index 0 to 6)
    If i <= 6 Then
        With rngArrayMain(i)
            .Font.Bold = True
            .Font.Size = 10
            .Font.Name = "Calibri"
            .Font.ColorIndex = 2 'white
        End With
    Else
        ' Last two items do this
        With rngArrayMain(i)
            .Font.Bold = True
            .Font.Size = 10
            .Font.Name = "Calibri"
            .Font.ColorIndex = 1
        End With
    End If
Next

注意:如果您删除了With块，那么您实际上会保存代码行，因为您将If逻辑放在一个不同的属性周围，如下所示：

For i As Integer = 0 To rngArrayMain.Length - 1
    rngArrayMain(i).Font.Bold = True
    rngArrayMain(i).Font.Size = 10
    rngArrayMain(i).Font.Name = "Calibri"

    If i <= 6 Then
        rngArrayMain(i).Font.ColorIndex = 2
    Else
        rngArrayMain(i).Font.ColorIndex = 1
    End If
Next

Answer 2

Dim cellsWithColor2 As Range
Dim cellsWithColor1 As Range

Dim xlWSEE As Worksheet
Set xlWSEE = Worksheets("FillTheNameHere")

Set cellsWithColor2 = Application.Union( _
    xlWSEE.Range("I40"), _
    xlWSEE.Range("V46"), _
    xlWSEE.Range("L48:N48"), _
    xlWSEE.Range("L51"), _
    xlWSEE.Range("J35"), _
    xlWSEE.Range("J53"), _
    xlWSEE.Range("B57:B61"), _
    xlWSEE.Range("B70"))

With cellsWithColor2
    .Font.Bold = True
    .Font.Size = 10
    .Font.Name = "Calibri"
    .Font.ColorIndex = 2 'white
End With


Set cellsWithColor1 = Application.Union( _
xlWSEE.Range("L47"), _
xlWSEE.Range("O47"))


With cellsWithColor1
    .Font.Bold = True
    .Font.Size = 10
    .Font.Name = "Calibri"
    .Font.ColorIndex = 1 'another color
End With