Excel VBA For Loop terminates too early

Question

Sub AssignNum()
    Dim iTotalCol As Integer
    Dim iNBRFirstRow As Integer
    Dim iLastRow As Integer
    Dim iHRISCol As Integer
    Dim strColLetter As String
    Dim iCount As Integer
    Dim iNum As Integer

    iNum = 1

    With shtReport
        iHRISCol = .Range("rngHRISID").Column
        iTotalCol = .Range("rngStart").Column
        iNBRFirstRow = .Range("rngHRISID").Row + 1
        iLastRow = .Cells(.Rows.Count, iHRISCol).End(xlUp).Row
        .Range("A" & iNBRFirstRow & ":A" & iLastRow).ClearContents

        'Assign Num number if total > 0 and Parent HRIS ID equals HRIS ID
        For iCount = iNBRFirstRow To iLastRow
            If .Cells(iCount, iTotalCol).Value > 0 And _
            .Cells(iCount, iHRISCol) = .Cells(iCount, iHRISCol - 1) Then
                 .Range("A" & iCount).Value = iNum
                 iNum = iNum + 1
            End If
        Next iCount
    End With
End Sub

Here, the value of iLastRow is 761 and iNBRFirstRow is 7. So, iCount should loop through 7 to 761, but it stops at 184, terminating the for loop too early. I just can't figure out what's causing this issue. Could anyone help?

There is a syntax error: the line starting with "=" should be one line above. Other than that, is .Cells(iCount, iTotalCol).Value >0 after row 184? (or .Cells(iCount, iHRISCol) = .Cells(iCount, iHRISCol - 1) — paul bica
– paul bica, Commented Apr 17, 2018 at 22:49
@paulbica thanks for comment, and in my original code line "=" is one line. and yes some values for .Cells(iCount, iTotalCol).Value >0 after row 184 — Jung
– Jung, Commented Apr 17, 2018 at 22:53
Add a Watch for iCount = 183, and select Break When Value Is True. Run the code and it should stop at iteration 183. Now step through the If statements with F8, confirm that both If conditions are met. The next iteration will be 184 - are both If conditions met? — paul bica
– paul bica, Commented Apr 17, 2018 at 23:13
@paulbica thanks for the comment! it looks like it terminated because the row at 184 had '#N/A'! — Jung
– Jung, Commented Apr 17, 2018 at 23:23

paul bica · Accepted Answer · 2018-04-17 23:28:43Z

Updated code to check for errors, and changed all Ints to Longs:

Option Explicit

Public Sub AssignNum()
    Dim iTotalCol As Long
    Dim iNBRFirstRow As Long
    Dim iLastRow As Long
    Dim iHRISCol As Long
    Dim strColLetter As String
    Dim iCount As Long
    Dim iNum As Long

    iNum = 1

    With shtReport
        iHRISCol = .Range("rngHRISID").Column
        iTotalCol = .Range("rngStart").Column
        iNBRFirstRow = .Range("rngHRISID").Row + 1
        iLastRow = .Cells(.Rows.Count, iHRISCol).End(xlUp).Row
        .Range("A" & iNBRFirstRow & ":A" & iLastRow).ClearContents

        'Assign Num number if total > 0 and Parent HRIS ID equals HRIS ID
        For iCount = iNBRFirstRow To iLastRow
            If Not IsError(.Cells(iCount, iTotalCol)) And _
               Not IsError(.Cells(iCount, iHRISCol)) And _
               Not IsError(.Cells(iCount, iHRISCol - 1)) Then
                    If .Cells(iCount, iTotalCol).Value > 0 And _
                       .Cells(iCount, iHRISCol) = .Cells(iCount, iHRISCol - 1) Then
                            .Range("A" & iCount).Value = iNum
                            iNum = iNum + 1
                    End If
            End If
        Next iCount
    End With
End Sub

Collectives™ on Stack Overflow

Excel VBA For Loop terminates too early

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related