CODEWITHSUNDEEP
vbaexcel
Ans
Ans

Reputation: 1234

Excel vba for loop changing loop variable inside a loop

Lets say I have a For loop similar to this:

Sub forLoop()
Dim firstRow As Integer
Dim lastRow As Integer
Dim aRow As Integer
firstRow=5
lastRow=200
For aRow = firstRow to lastRow
   If (something) Then  //lets say it happened when aRow = 18
       aRow=aRow+1  //=> aRow=19 
       ...
   End If
Next aRow  //which one ? will next aRow be 19 or 20?

And I increment a aRow variable inside the loop. Would it result in that variable being incremented twice as much?

Upvotes: 0

Views: 1438

Answers (3)

Gary's Student
Gary's Student

Reputation: 96753

You will get a one-time bump:

Sub forLoop()
    Dim firstRow As Integer
    Dim lastRow As Integer
    Dim aRow As Integer
    Dim i As Long

    firstRow = 5
    lastRow = 200
    i = 1

    For aRow = firstRow To lastRow
       If (aRow = 18) Then
           aRow = aRow + 1
       End If
       Cells(i, "A") = aRow
       i = i + 1
    Next aRow
End Sub

enter image description here

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152475

Yes, it does.

Run the this simple script:

Sub forLoop()
Dim i As Long
For i = 1 To 100
    If i Mod 5 = 0 Then
        i = i + 1
    End If

Debug.Print i
Next i
End Sub

As you can see with the output, when i is 5 it is forced to 6 and then the next loop is 7.

enter image description here

Upvotes: 1

Mrig
Mrig

Reputation: 11702

If you'll debug your code you can see the value yourself. Below code results in

Sub Demo()
    Dim firstRow As Integer
    Dim lastRow As Integer
    Dim aRow As Integer
    firstRow = 5
    lastRow = 200
    For aRow = firstRow To lastRow
        If aRow = 7 Then
            aRow = aRow + 1
        End If
        Debug.Print aRow
    Next aRow
End Sub

output as

enter image description here

Here, aRow = aRow + 1 increments the value of aRow by 1 and is subsequently reflected in loop.

Upvotes: 2

Related Questions