CODEWITHSUNDEEP
excelvbafor-loop
Scott
Scott

Reputation: 79

Why is my For Loop exiting before last row?

I have a fairly simple For loop that is looking at 2 columns - NAME and JOB. If there are comma delimited names in a cell, it triggers a function that inserts each name one row below, and then takes the number of new rows and adds it to the last row value (minus 1). Then, if there is no JOB next to that name, it uses the JOB value +1 above.

Problem I am running into is that even though I can see that the LastRow value is being added to as I step through, the loop still exits after it reaches the original LastRow value when the loop began.

i.e. if LR = 100 at the beginning of the loop, but because of CountUnique, LR now = 115, the loop still exits after 100.

I cannot figure out why.

 LR = Range("B" & Rows.Count).End(xlUp).Row

' Mirror missing attributes
    For i = 2 To LR
        If InStr(Cells(i, 2).Value, ",") Then
            LR = LR + CountUnique(Cells(i, 2)) - 1
        End If
        If Cells(i, 1).Value = "" Then
            Cells(i, 1).Value = Cells(i - 1, 1).Value
        End If

    Next i

Public Function CountUnique(r As Range) As Integer

  Dim c As Collection
    Set c = New Collection
    ary = Split(r.Text, ",")
    On Error Resume Next
    For Each a In ary
        c.Add a, CStr(a)
        If Err.Number = 0 Then

            If CountUnique >= 1 Then
                r.Offset(CountUnique, 0).EntireRow.Insert
                r.Offset(CountUnique, 0).Value = Trim(a)
            End If

            CountUnique = CountUnique + 1
        Else
            Err.Number = 0
        End If
    Next a

 r.Value = c.Item(1)

End Function

Upvotes: 0

Views: 206

Answers (1)

GSerg
GSerg

Reputation: 78210

In VB, the boundaries of the For loop are evaluated once at the start and the values are cached.

If you want a dynamic upper bound, you need to use a Do loop.

Upvotes: 1

Related Questions