CODEWITHSUNDEEP
vb.net
Jose M.
Jose M.

Reputation: 2340

For Loop and If statement. How to combine in one statement

I have the following for loop on my project:

'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

No problem, works well. However, I have to separate the last two elements of my array because the Font.ColorIndex = 1 for them.

So basically my for loop would be: if array 0-7 perform this action

if array 8-9 then perform this action.

How can I fix my code to reflect this change? If the question seems elementary it's because I have not been programming long and what I know is self-taught (google, bing, and a few books).

Thanks.

Upvotes: 0

Views: 225

Answers (2)

shahkalpesh
shahkalpesh

Reputation: 33474

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

Upvotes: 1

Karl Anderson
Karl Anderson

Reputation: 34846

Use a For loop instead of a For Each, like this:

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

Note: If you remove the With block, then you would actually save lines of code, because you put the If logic around the one property that is different, like this:

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

Upvotes: 3

Related Questions