我試圖使用 VBA 代碼對商品價格求和以找到總價和最終總價之間的差異，差異應該需要粘貼在可調列中，但這里的輸出通過迭代給出了所有總和值
這是我的代碼：
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
問題的資料：
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
圖片：
結果要么顯示每個客戶的小計和總計的所有資料，要么顯示小計和總計，或僅顯示總計（單擊數字1，2或3在左邊距中）。
這個“小計”功能的開始可以記錄在一個宏中，你會得到類似的東西：
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
uj5u.com熱心網友回復：
請嘗試下一個代碼。未經測驗，但如果我正確理解了問題，它應該可以作業：
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
如果您需要四舍五入回傳的總和以不顯示小數（正如您在問題中向我們展示的那樣），我可以調整代碼來做到這一點。現在，它只添加它們并顯示所有結果小數。
請測驗它并發送一些反饋。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/328274.html
下一篇：VlookupwithMatch和NotMatch在VBA中回傳特定值。請找到以下代碼。我需要知道如何獲得匹配值