CODEWITHSUNDEEP
excelvba
Pokey124
Pokey124

Reputation: 23

Excel VBA For loop looping too early

When I step through the code, after the line "Cells(r_count, 5) = temp + totalDays", it goes back to the beginning of the "For c_count" loop, and the code never gets to the Next part, and so the counter is not incrimented.

For c_count = 7 To 42 Step 5
    For r_count = 4 To 80
        If Cells(r_count, c_count) = "pass" Then
            If Not (IsEmpty(Cells(r_count, (c_count + 1)))) Then
                If Not (IsEmpty(Cells(r_count, (c_count + 2)))) Then
                    s_date = Cells(r_count, (c_count + 1))
                    e_date = Cells(r_count, (c_count + 2))
                    totalDays = DateDiff("d", s_date, e_date)
                    temp = Cells(r_count, 5)
                    Cells(r_count, 5) = temp + totalDays
                End If
            End If
        End If
    Next r_count
Next c_count

I have to be overseeing something stupid in my code. Should I make my IF statements into one instead of multiple statements? Any help?

Upvotes: 1

Views: 200

Answers (1)

Niall
Niall

Reputation: 1621

Is this code being called by the Worksheet_Change event by any chance? I'm assuming so because you say on the Cells(r_count, 5) = temp + totalDays line it goes back to the beginning - this is because this line sets the cell's value hence triggering the Worksheet_Change event and that's also why it eventually finishes.

If so then you can set Application.EnableEvents to False while updates are carried out (actually Philip's suggestion). For example:

Private Sub Worksheet_Change()

    Application.EnableEvents = False

    ....your code here....

    Application.EnableEvents = True

End Sub

Upvotes: 2

Related Questions