- 帖子
- 1425
- 主題
- 40
- 精華
- 0
- 積分
- 1449
- 點名
- 0
- 作業系統
- Windows 7
- 軟體版本
- Excel 2010 & 2016
- 閱讀權限
- 50
- 性別
- 男
- 來自
- 台灣
- 註冊時間
- 2020-7-15
- 最後登錄
- 2024-4-16
|
16#
發表於 2023-6-28 14:44
| 只看該作者
回復 15# cypd
謝謝前輩回復
後學學習方案如下,請前輩參考
執行結果:
Sub TEST()
Dim Brr, V4&, V5&, Z, i&, T$, A&, Af&, B$, Bn&, D&, Df, Ra As Range
Set Z = CreateObject("Scripting.Dictionary")
Set Ra = Range([工作表1!H3], [工作表1!A65536].End(3)): Brr = Ra
For i = 1 To UBound(Brr)
T = Brr(i, 3): V4 = Val(Brr(i, 4)): V5 = Val(Brr(i, 5))
If V4 > 0 Then
A = Z(T & "|進"): A = A + V4: Z(T & "|進") = A
Af = Z(T & "|進額"): Af = Af + V4 * Val(Brr(i, 6))
Z(T & "|進額") = Af
End If
Bn = Val(Brr(i, 8))
If Bn > 0 Then
B = Z(T & "|贈敘")
Z(T & "|贈數") = Z(T & "|贈數") + Bn
If B = "" Then
B = Brr(i, 1) & "項_" & Brr(i, 2) & "_贈_" & Bn
Else
B = B & " ★" & Brr(i, 1) & "項_" & Brr(i, 2) & "_贈_" & Bn
End If
Z(T & "|贈敘") = B: B = ""
End If
If V5 > 0 Then
D = Z(T & "|銷"): D = D + V5: Z(T & "|銷") = D
Df = Z(T & "|銷額"): Df = Df + V5 * Val(Brr(i, 6))
Z(T & "|銷額") = Df
End If
Next
Set Ra = Range([工作表1!V2], [工作表1!N65536].End(3)): Brr = Ra
For i = 2 To UBound(Brr)
T = Brr(i, 1)
Brr(i, 2) = Z(T & "|進")
Brr(i, 3) = Z(T & "|銷") + Z(T & "|贈數")
Brr(i, 4) = Brr(i, 2) - Brr(i, 3)
Brr(i, 5) = Z(T & "|進額")
Brr(i, 6) = Z(T & "|銷額")
Brr(i, 7) = Brr(i, 6) - Brr(i, 5)
Brr(i, 8) = "共贈出: " & Z(T & "|贈數")
Brr(i, 9) = Z(T & "|贈敘")
Next
Ra = Brr: [U2] = "備註"
Set Z = Nothing: Erase Brr: Set Ra = Nothing
End Sub |
|
