VBA Code Sum the price of each customer items-CodePudding

I was trying to sum the items price to find the difference between total price and final sum price using VBA code the difference should need paste in adjustable column but here the output gives all the sum values through iteration

here is my code:

Const start_row = 2
Const tot_price = 3
Const cus_id_num = 1
Sub refresh()
    Dim cus_id      As String
    Dim fee_item    As String
    Dim temp        As Variant
    
    i = start_row
    j = 1
    cus_id = Me.Cells(i, 1).Value
    While Me.Cells(i, 2).Value <> ""
        If Me.Cells(i, 1).Value <> "0" Then
            If cus_id <> Me.Cells(i, 1).Value Then
                cus_id = Me.Cells(i, 1).Value
            End If
            Debug.Print cus_id
            ''Debug.Print cus_id
            If cus_id <> "" Then
                If Me.Cells(i, tot_price).Value <> "" Then
                    base_fee = Me.Cells(i, tot_price).Value
                End If
                Debug.Print base_fee
                temp = 0
            Else
                If Me.Cells(i, 1).Value = "" Then
                    fee_item = Round(Me.Cells(i, tot_price).Value, 0)
                    temp = fee_item   temp
                End If
                Debug.Print temp
            End If
        End If
        i = i   1
    Wend
End Sub

data for the problem:

Customer_id_number||Items            ||      Total Price || Adjustable
70                ||  groceries      ||         1578.00  ||  
----------------------------------------------------------------------
                   Detergent Powder             719.00
----------------------------------------------------------------------
                   Detergent Soap               58.00
----------------------------------------------------------------------
                   Floor cleaner liquid         396.00
 ---------------------------------------------------------------------
                   Tooth Paste                  58.14
 ---------------------------------------------------------------------
                   Tooth Brush                  27.51
 --------------------------------------------------------------------
                   Shampoo                      219.77
 ---------------------------------------------------------------------
                   Perfume                      100.00
 ---------------------------------------------------------------------
40                 vegetable and fruits         1370.00
----------------------------------------------------------------------
                   Tomatoes                     88.00
----------------------------------------------------------------------
                   Onions                       38.00
----------------------------------------------------------------------
                   Sweet Potatoes               93.00
----------------------------------------------------------------------
                   Oranges                      809.00
----------------------------------------------------------------------
                   Mangoes                      282.03
----------------------------------------------------------------------
                   Spinach                      59.90

Image:

The results either shows all data with subtotals per customer and grand total, or just subtotals and grand total, or just the grand total (click on the numbers 1, 2 or 3 in the left margin).

The starting of this "subtotals" feature can be recorded in a macro, you'll get something like:

Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _
    Replace:=True, PageBreaks:=False, SummaryBelowData:=True

CodePudding user response：

Please, try the next code. Not tested, but it should work, if I correctly understood the question:

Sub testSummaryzePerCustID()
   Dim sh As Worksheet, lastR As Long, arr, arrFin, i As Long, k As Long
   
   Set sh = ActiveSheet
   lastR = sh.Range("B" & sh.rows.count).End(xlUp).row
   arr = sh.Range("A2:C" & lastR).Value    'Place the range in an array for faster iteration
   ReDim arrFin(1 To UBound(arr), 1 To 1) 'reDim the final array to have the same number of rows as arr
   For i = 1 To UBound(arr)       'iterate between the array elements
        If arr(i, 1) = "" Then    'for cases of nullString in first column:
            If k = 1 Then         'first time it uses the value in the third collumn
                arrFin(i, 1) = arr(i, 3): k = k   1
            Else
               arrFin(i, 1) = arrFin(i - 1, 1)   arr(i, 3) 'then it adds the values in the third column to the previous arrFin value
            End If
        Else
            arrFin(i, 1) = arr(i, 1) & " " & arr(i, 3): k = 1 'concatenate ID with value of the third column
        End If
   Next i
   'drop the final array content at once:
   sh.Range("D2").Resize(UBound(arrFin), 1).Value = arrFin
End Sub

If you need to round the returned sums to not show decimals (as you show us in your question), I can adapt the code to do it. Now, it only adds them and shows all resulted decimals.

Please, test it and send some feedback.