- 帖子
- 1446
- 主題
- 40
- 精華
- 0
- 積分
- 1470
- 點名
- 0
- 作業系統
- Windows 7
- 軟體版本
- Excel 2010 & 2016
- 閱讀權限
- 50
- 性別
- 男
- 來自
- 台灣
- 註冊時間
- 2020-7-15
- 最後登錄
- 2024-10-21
|
5#
發表於 2023-6-1 13:22
| 只看該作者
謝謝論壇,謝謝各位前輩
後學藉此帖練習陣列與字典,學習方案如下,請各位前輩指教
交期表:
WIP表執行前:
WIP表執行結果:
Option Explicit
Sub TEST() '↑
Dim Arr, Brr, Crr, V$, Y, R&, i&, S&, T1$, T3$
Set Y = CreateObject("Scripting.Dictionary")
Brr = Range([交期!C2], [交期!A65536].End(xlUp))
For i = 1 To UBound(Brr): Y(Brr(i, 1)) = Y(Brr(i, 1)) & "/" & i: Next
Crr = Range([WIP!C2], [WIP!A65536].End(xlUp))
ReDim Arr(1 To UBound(Crr), 1 To 1)
For i = 1 To UBound(Crr)
T1 = Crr(i, 1): T3 = Crr(i, 3)
If Y(T1) = "" Then Arr(i, 1) = "無訂單": GoTo i01
i00: S = Y(T1 & "|餘量")
If S <= 0 Then
V = Mid(Y(T1), InStr(Y(T1), "/") + 1) & "/": R = Val(V)
If R = 0 Then Arr(i, 1) = "無續單": GoTo i01
Y(T1) = V: Y(T1 & "|餘量") = Brr(R, 3) + S
If Y(T1 & "|餘量") <= 0 Then GoTo i00
Y(T1 & "|交期") = Brr(R, 2)
End If
Arr(i, 1) = Y(T1 & "|交期")
Y(T1 & "|餘量") = Y(T1 & "|餘量") - Crr(i, 3)
i01: Next
[WIP!D2].Resize(UBound(Arr)) = Arr
Set Y = Nothing: Erase Arr, Brr, Crr
End Sub |
|
