- 帖子
- 1567
- 主題
- 40
- 精華
- 0
- 積分
- 1591
- 點名
- 0
- 作業系統
- Windows 7
- 軟體版本
- Excel 2010 & 2016
- 閱讀權限
- 100
- 性別
- 男
- 來自
- 台灣
- 註冊時間
- 2020-7-15
- 最後登錄
- 2026-2-2
|
本帖最後由 Andy2483 於 2023-4-18 14:28 編輯
謝謝論壇,謝謝各位前輩
後學藉此帖練習陣列與字典,變更情境為模擬出貨結餘或不足,
學習方案如下,請各位前輩指教
執行前:
執行結果:
Option Explicit
Sub TEST()
Dim Brr, Y, Lh&, Lj&, i&, Tg$, xR As Range
Set Y = CreateObject("Scripting.Dictionary")
Set xR = Range([J1], Cells(Rows.Count, "G").End(xlUp)): Brr = xR
For i = 2 To UBound(Brr)
If i = 2 Then Brr(1, 1) = "訂單量累加": Brr(1, 2) = "虛擬出貨後庫存": Brr(1, 3) = "備註"
Tg = Brr(i, 1): Lh = Brr(i, 2): Lj = Brr(i, 4)
If Y(Tg) = "" Then
Y(Tg) = Lh: Y(Tg & "庫存") = Lj
Else: Y(Tg) = Y(Tg) + Lh
End If
Y(Tg & "庫存") = Y(Tg & "庫存") - Lh
Brr(i, 1) = Y(Tg)
Brr(i, 2) = Y(Tg & "庫存")
If Brr(i, 2) < 0 Then
Brr(i, 3) = Tg & "_庫存數不足累計 " & -Brr(i, 2)
ElseIf Brr(i, 2) = 0 Then Brr(i, 3) = Tg & "_0庫存"
Else: Brr(i, 3) = Tg & "_庫存數結餘 " & Brr(i, 2)
End If
Next
With Intersect(xR.Offset(0, 4), [K:M])
.EntireColumn.ClearContents
.Value = Brr
End With
Set Y = Nothing: Set xR = Nothing: Erase Brr
End Sub |
|
發表於 2023-4-18 14:20
| 