CODEWITHSUNDEEP
vbaexcel
Jung
Jung

Reputation: 209

Excel VBA For Loop terminates too early

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?

Upvotes: 0

Views: 313

Answers (1)

paul bica
paul bica

Reputation: 10705

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

Upvotes: 1

Related Questions