Do While Loop - Sum - Paste only partially working

Question

I am trying to automate an upload file from a dataset. Depending on the client - we will upload nett or gross sums quantities. I want to run a macro that will do the below:

1. Check if the client is gross or nett
2. If net, then sum quantities together for all rows that below to this client (data will be sorted already) and paste into the relevant column on sheet 2 - then move onto the next row/client
3. If gross, then copy just for this row, paste into the relevant column and then move onto the next row

My code is below:

Sub netting()
    
    Dim od As Worksheet
    Dim eb As Worksheet
    Dim mapping As Worksheet
    Dim client As String
    Dim currentvalue As Double
    Dim row As Long
    Dim i As Long
    Dim p As Long

    Set od = ThisWorkbook.Sheets("Original Data")
    Set eb = ThisWorkbook.Sheets("EB Bulk Upload")
    Set mapping = ThisWorkbook.Sheets("Mapping")
    
    lastrow = od.Cells(od.Rows.Count, "H").End(xlUp).row
    
    i = 20
    p = 2
    
    For i = 20 To lastrow
    
    Dim clientid As String
    clientid = od.Cells(i, "E").Value
    
    Dim nettvalue As Variant
    nettvalue = Application.WorksheetFunction.XLookup(clientid, mapping.Range("$B$2:$B$287" & lastRow), mapping.Range("$C$2:$C$287" & lastRow))
        
            If nettvalue = "Y" Then
                Dim sum As Double
                sum = 0
                
                Do While od.Cells(i, "E").Value = clientid
                    sum = sum + od.Cells(i, "H").Value
                    i = i + 1
                Loop
            
                eb.Cells(p, "F").Value = sumValue
                
            ElseIf nettvalue = "N" Then
                
                eb.Cells(p, "F").Value = od.Cells(i, "H").Value
                
            End If
            
            i = i + 1
            p = p + 1
            
    Next i

    
End Sub

What actually happens now is:

• the first client quantity is copied correctly
• the second client skips row 21 and then sums the following rows
• the macro then does not work on the next clients.

An example of what I want to happen is below:

Source Data

Column A	Column B
Client 1	-10
Client 2	50
Client 2	-25
Client 2	10
Client 3	10
Client 3	5
Client 4	100

Finished Data:

Column A	Column B
Client 1	-10
Client 2	35
Client 3	15
Client 4	100

Answer 1

Your code has nested loops (For+Do While), which requires care when modifying the iterators. The outer loop increments i by 1 each iteration (Next i). The inner Do loop breaks when it reaches a line with a different clientId. Multiple lines are incrementing i within the nested loops. This results in skipped rows by jumping iterations.

Change two lines

    For i = 20 To lastrow
        Dim clientid As String
        clientid = od.Cells(i, "E").Value
        Dim nettvalue As Variant
        nettvalue = Application.WorksheetFunction.XLookup(clientid, mapping.Range("$B$2:$B$287" & lastRow), mapping.Range("$C$2:$C$287" & lastRow))
        If nettvalue = "Y" Then
            Dim sum As Double
            sum = 0
            Do While od.Cells(i, "E").Value = clientid
                sum = sum + od.Cells(i, "H").Value
                i = i + 1
            Loop
            eb.Cells(p, "F").Value = sumValue
            i = i - 1 ' ** new code
        ElseIf nettvalue = "N" Then
            eb.Cells(p, "F").Value = od.Cells(i, "H").Value
        End If
        ' i = i + 1 ** remove it
        p = p + 1
    Next i