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 :( .

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... — Tim Williams
– Tim Williams, Commented Aug 23, 2013 at 19:43

Community · Accepted Answer · 2017-05-23 10:32:15Z

2

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

edited May 23, 2017 at 10:32

CommunityBot

11 silver badge

answered Aug 23, 2013 at 19:46

enderland

14.2k18 gold badges105 silver badges157 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DanW Over a year ago

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

Collectives™ on Stack Overflow

Using an if statement within a for loop- Excel VBA

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related