CODEWITHSUNDEEP
excelvba
Matiss Zuravlevs
Matiss Zuravlevs

Reputation: 379

in VBA For loop stops executing after around 530 iterations

I have written simple code for my job related tasks, but it stops executing without any error message on 530 iteration, while I still have some data left that should be processed.

Tried to delete all code in VBA and paste it in from Notepad. Tried debugger. Tried restarting excel and pc.

Function CoRow() As Long
    CoRow = Cells(Rows.Count, 1).End(xlUp).Row
End Function

Sub Sort()
    Dim LastNace As Integer
    Dim NextNace As Integer
    Dim i As Long
    LastNace = Cells(2, "C").Value
    NextNace = Cells(3, "C").Value
    Columns("A:E").Select
    Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Key2:=Range("E2"), Order2:=xlDescending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
    For i = 1 To CoRow
        If LastNace <> NextNace And LastNace <> 0 And NextNace <> 0 And i <> 1 Then
            Rows(i + 1).EntireRow.Insert
            Range(Cells(i + 1, 1), Cells(i + 1, 5)).Interior.Color = RGB(255, 255, 0)
            i = i + 1
        ElseIf LastNace <> NextNace And LastNace <> 0 And NextNace = 0 And i <> 1 Then
            Rows(i + 1).EntireRow.Insert
            Range(Cells(i + 1, 1), Cells(i + 1, 5)).Interior.Color = RGB(255, 255, 0)
            i = i + 1
        End If
        LastNace = Cells(i + 1, "C").Value
        NextNace = Cells(i + 2, "C").Value
        'Range(Cells(i + 1, 3).Address(), Cells(i + 1, 3).Address()).Interior.Color = RGB(255, 0, 0)
    Next i
End Sub

Expected result is that goes beyond 530 iterations. I suspect there is something wrong with sort, as it sort also same number of rows, before this code is executed.

Upvotes: 2

Views: 1228

Answers (2)

Matiss Zuravlevs
Matiss Zuravlevs

Reputation: 379

Thanks guys. I implemented a lot from your suggestions and now this code does what I wanted it to do. :)

Function CoRow() As Long
CoRow = Cells(Rows.count, 1).End(xlUp).Row
End Function

Sub Sort()
Dim LastNace As Integer
Dim NextNace As Integer
Dim CountNace As Integer
Dim r As Long
Dim i As Long
Sheets("Imp").Range("A:E").Sort Key1:=Range("C2"), Order1:=xlAscending, Key2:=Range("E2"), Order2:=xlDescending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
LastNace = Sheets("Imp").Cells(2, "C").Value
NextNace = Sheets("Imp").Cells(3, "C").Value
r = CoRow
CountNace = 0
For i = 1 To r
    If LastNace <> NextNace And LastNace <> 0 And NextNace <> 0 Then
        CountNace = CountNace + 1
    End If
    LastNace = Sheets("Imp").Cells(i + 1, "C").Value
    NextNace = Sheets("Imp").Cells(i + 2, "C").Value
Next
r = r + CountNace
LastNace = Sheets("Imp").Cells(2, "C").Value
NextNace = Sheets("Imp").Cells(3, "C").Value
For i = 1 To r
    If LastNace <> NextNace And LastNace <> 0 And NextNace <> 0 And i <> 1 Then
        Sheets("Imp").Rows(i + 1).EntireRow.Insert
        Sheets("Imp").Range(Cells(i + 1, 1), Cells(i + 1, 5)).Interior.Color = RGB(255, 255, 0)
        i = i + 1
    ElseIf LastNace <> NextNace And LastNace <> 0 And NextNace = 0 And i <> 1 Then
        Sheets("Imp").Rows(i + 1).EntireRow.Insert
        Sheets("Imp").Range(Cells(i + 1, 1), Cells(i + 1, 5)).Interior.Color = RGB(255, 255, 0)
        i = i + 1
    End If
    LastNace = Sheets("Imp").Cells(i + 1, "C").Value
    NextNace = Sheets("Imp").Cells(i + 2, "C").Value
    'Sheets("Imp").Range(Cells(i + 1, 3), Cells(i + 1, 3)).Interior.Color = RGB(255, 0, 0)
Next
End Sub

Upvotes: 1

Pᴇʜ
Pᴇʜ

Reputation: 57683

Your recalculation of CoRow doesn't effect the end of the loop!

Note that in For loops, once the loop is started

For i = 1 To CoRow

any value change of CoRow does not affect the end of the loop! The For loop always uses the value of CoRow that was set when the loop started.

Following example:

Dim i As Long
Dim iEnd As Long
iEnd = 10

For i = 1 To iEnd
    iEnd = 20 'this has NO EFFECT on the end of the For loop
    Debug.Print i, iEnd
Next i

This loob will only run from 1 … 10 because once the loop started with For i = 1 To iEnd any change of iEnd = 20 doesn't affect the end of the loop.

Solution

Replace it with a Do loop.

Dim i As Long
Dim iEnd As Long
iEnd = 10

i = 1 'initialization needed before Do loops

Do While i <= iEnd
    iEnd = 20
    Debug.Print i, iEnd

    i = i + 1 'manual increase of counter needed in the end of Do loops
Loop

Note that for Do loops you need to initialize your counter i = 1 and also increase it manually i = i + 1. This time the change of iEnd = 20 take effect and the loop runs from 1 … 20 because the Do loop evaluates the condition i <= iEnd on every iteration (not only on the start as the For loop does).

Alternative

Another solution (if you insert or delete rows) is to run the loop backwards:

Dim CoRow As Long 'make it a variable not a function then
CoRow = Cells(Row.Count, 1).End(xlUp).Row

Dim i As Long
For i = CoRow To 1 Step -1
    'runs backwards starting at the last row ending at the first
Next i

But if this is possible or not depends on your data and what actions you perform within the loop.

Improvement

Note that this CoRow = Cells(Rows.Count, 1).End(xlUp).Row will eat up some time. Instead of making CoRow a function make it a variable and just increase it by 1 CoRow = CoRow + 1 every time you insert a row, which will be much faster than determining the last row over and over.

Upvotes: 3

Related Questions