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:
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:
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 |
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