- 帖子
- 1425
- 主題
- 40
- 精華
- 0
- 積分
- 1449
- 點名
- 0
- 作業系統
- Windows 7
- 軟體版本
- Excel 2010 & 2016
- 閱讀權限
- 50
- 性別
- 男
- 來自
- 台灣
- 註冊時間
- 2020-7-15
- 最後登錄
- 2024-4-16
|
14#
發表於 2024-1-31 16:35
| 只看該作者
回復 8# 准提部林
謝謝論壇,謝謝前輩指導
後學藉此帖練習陣列與字典,學習方案如下,請前輩再指導
Option Explicit
Sub TEST()
Dim Arr, Brr, Crr, 需求&, Z, i&, j%, C%, T$, 庫存&, D As Date, W&, V&, R&
Set Z = CreateObject("Scripting.Dictionary")
Brr = Range([庫存!C1], [庫存!A65536].End(3))
For i = 2 To UBound(Brr)
T = Trim(Brr(i, 1)): If T = "" Then GoTo i01
If Not IsObject(Z(T)) Then Set Z(T) = CreateObject("Scripting.Dictionary")
Z(T)(i) = 0: Z(T & "Tot") = Z(T & "Tot") + Val(Brr(i, 3))
i01: Next
Crr = Range([原始!C1], [原始!A65536].End(3))
ReDim Arr(2 To UBound(Crr), 1 To 100)
For i = 2 To UBound(Crr)
T = Trim(Crr(i, 2)): 需求 = Val(Crr(i, 3)): C = 0: If Z(T & "Tot") = 0 Then GoTo i02
For j = Z(T & "No") To Z(T).Count - 1
W = W + 1: R = Z(Trim(Crr(i, 2))).keys()(j)
庫存 = Val(Brr(R, 3))
D = CDate(Brr(Z(Trim(Crr(i, 2))).keys()(j), 2))
V = IIf(庫存 < 需求, 庫存, 需求)
Arr(i, C + 1) = D: Arr(i, C + 2) = V: C = C + 2
Z(T & "Tot") = Z(T & "Tot") - V
需求 = 需求 - V: 庫存 = 庫存 - V
If 庫存 = 0 Then Z(T & "No") = Z(T & "No") + 1 Else Brr(R, 3) = 庫存
If 需求 = 0 Then Exit For
Next
i02: If Z(T & "Tot") = 0 And 需求 > 0 Then Arr(i, C + 1) = "沒有資料": Arr(i, C + 2) = "數量不足"
Next
[原始!D2].Resize(UBound(Arr) - 1, UBound(Arr, 2)) = Arr
MsgBox "迴圈數:" & W
End Sub |
|
發表於 2020-4-4 09:44
| 
