For-loop with multiple if-conditions does not complete correctly

Question

I have written a relatively simple procedure which is meant to do the following:

• There are two columns on my spreadsheet, B and C. Both data sets start in row 26
• B contains the number of a project (e.g. Project 1), and C contains the identifier for each line item so that multiple services can be allocated to a project. The combination of both variables will then form a unique ID for each line item (e.g. 1/3 for the third item in Project 1)
• Every time a new project ID is entered in column B, column C should start again at 1
• Once the list of projects finishes, the loop should stop (first if-condition)
• I use another procedure to sort the data set by project number and then another criterium, which means that I also have to rewrite column C each time afterwards
• to make the code easier to read I created ThisRow and LastRow

My code does not produce an error message, but it does not return the desired results either: Cell C27 should have a value of 2, but it returns 1 and all cells below in the same column are blank. This seems strange since in the Immediate Window they all get a value of 1 (which is still not correct, but different to what it prints to the spreadsheet).

I have tried for a while to fix it but I am at a loss as to where the error may be. I appreciate your help a lot! Code below:

Sub DataSort()

Dim i As Object, ThisRow As Integer , LastRow As Integer

Range("C26").Value = 1
Range("C27").Activate    
ThisRow = ActiveCell.Offset(0, -1).Value

LastRow = ActiveCell.Offset(-1, -1).Value


    For Each i In Range("ProcessID")
        If ThisRow = 0 Then
            Exit Sub
        ElseIf ThisRow > LastRow Then
            ActiveCell.Value = ActiveCell.Offset(0, -1).Value + 1
        Else
            ActiveCell.Value = 1
        End If
        Debug.Print ActiveCell.Value
    Next i
End Sub

>Update: Here is a screenshot of the spreadsheet; hopefully that helps:

Answer 1

You are looping on the same set of cells because you never change the active cell in your loop. You also need to recalculate the values of ThisRow and LastRow for every row, so it needs to be included in the loop.

Sub DataSort()

Dim i As Range, ThisRow As Integer , LastRow As Integer

' Set the initial active cell and first process number
Range("C26").Value = 1
Range("C27").Activate    

   ' Loop through each cell in the Process column  
    For Each i In Range("ProcessID")
        ' Load the value of this row and the last row
        ThisRow = ActiveCell.Offset(0, -1).Value
        LastRow = ActiveCell.Offset(-1, -1).Value

        ' Check if we are at the change of a project - update process number
        If ThisRow = 0 Then
            Exit Sub              
        ElseIf ThisRow > LastRow Then
            ActiveCell.Value = 1
        Else
            ActiveCell.Value = ActiveCell.Offset(0, -1).Value + 1
        End If
        ' Move the active cell down one row
        ActiveCell.Offset(1,0).Activate
    Next i
End Sub

Note: I haven't tested this code.

However your code doesn't go near the i variable. Using that, we don't need to use ActiveCell

Sub DataSort()

Dim i As Range, ThisRow As Integer , LastRow As Variant



   ' Loop through each cell in the Process column  
    For Each i In Range("ProcessID")
        ' Load the value of this row and the last row
        ThisRow = i.Offset(0, -1).Value
        LastRow = i.Offset(-1, -1).Value

        ' Check if we are at the change of a project - update process number
        If ThisRow = 0 Then
            Exit Sub
        ' First Row of Data
        ElseIf LastRow = "Project ID" Then
            i.Value = 1
        'Change of Project - reset Process ID
        ElseIf ThisRow > LastRow Then
            i.Value = 1
        'Same Project so increase the Process ID
        Else
            i.Value = i.Offset(-1, 0).Value + 1
        End If
        ' Move the active cell down one row
    Next i
End Sub

I think you may have had your logic swapped around as well when checking if ThisRow is greater than Last row - that should mean there is a change of project so reset the Process ID number.