Using an if statement within a for loop- Excel VBA

Question

I'm having trouble using an if statement inside a for loop in excel vba. The output of the debugger is not what I expect. I can post the full code of what I am trying to accomplish, but I think I have narrowed it down to what I don't understand. Here is my code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Integer

For i = 9 To 60 Step 3

If Cells(i, "DR").Value < Cells(i, "EB").Value Then
    Debug.Print i & "-ifloopstart"
    Cells(i, "DR").Value = 999999
    Debug.Print i & "-ifloopend"
End If

Next i

End Sub

The output of the debugger is:

9-ifloopstart
33-ifloopstart
51-ifloopstart
51-ifloopend
33-ifloopend
9-ifloopend

However, I expected:

9-ifloopstart
9-ifloopend
33-ifloopstart
33-ifloopend
51-ifloopstart
51-ifloopend

Can someone explain how this works? It seems to be looping back to the beginning of the if statement instead of finishing the if statement. How can I modify the code to get the output I expect? I've been struggling with this for hours and it seems so simple :( .

Solution

Each time the worksheet is updated with Cells(i, "DR").Value = 999999, Worksheet_Change gets called again.

Think of it this way. Each time that above code gets called, you modify a cell, which triggers the worksheet change method again.

So you are effectively nesting three calls of this function:

• called once, with i = 9
  • called again, with i = 33
    • called again, with i = 51
    • finishes i = 51 call
  • finishes i = 33 call
• finishes i = 9 call

VBA then goes backwards from each of these to get back to the first time your method was run.

Edit: as Tim says you can disable this with Application.EnableEvents=False

Comments

[Tim Williams]

updating the sheet will again trigger the event code, so you should use Application.EnableEvents=False before making any changes. Set back to True when done...

[DanW]

wow, big thanks to everyone who answered!... I've been going crazy here.