CODEWITHSUNDEEP
excelvba
BossRoyce
BossRoyce

Reputation: 211

How Do I Get Out of Nested Loop?

I need help in controlling reiteration of loops in the nested If...then statement. How do I make sure the operation happens only once for i=5 and i=6?

For i = 5 To 26
    Set sht = wkbk.Sheets(i)
    sht.Cells(3, 1).Value = "'" & StrConv(inputbx, vbProperCase) & " 2017"
    If i = 5 Or 6 Then
        Cells(7, 6).End(xlToRight).EntireColumn.Select
        Selection.AutoFill Destination:=Range(Selection, Selection.Offset(0, 1))
    End If
Next i

Upvotes: 0

Views: 68

Answers (2)

Scott Craner
Scott Craner

Reputation: 152450

Change the if and make sure your are referencing the correct sheet:

Dim clm As Long
For i = 5 To 26
    With wkbk.Sheets(i)
        .Cells(3, 1).Value = "'" & StrConv(inputbx, vbProperCase) & " 2017"
        If i = 5 Or i = 6 Then
            clm = .Cells(7, 6).End(xlToRight).Column
            .Columns(clm).AutoFill Destination:=.Range(.Columns(clm), .Columns(clm).Offset(0, 1))
        End If
    End With
Next i

Upvotes: 1

Luca Arcara
Luca Arcara

Reputation: 46

and add a new variable if you want that nested if happens only once for 5 and 6:

Dim to_work = true
For i = 5 To 26
    Set sht = wkbk.Sheets(i)
    sht.Cells(3, 1).Value = "'" & StrConv(inputbx, vbProperCase) & " 2017"
    If (i = 5 Or i = 6) and to_work Then
        sht.Cells(7, 6).End(xlToRight).EntireColumn.Select
        Selection.AutoFill Destination:=Range(Selection, Selection.Offset(0, 1))
        to_work = false
    End If
Next i

Upvotes: 0

Related Questions