- 帖子
- 2834
- 主題
- 10
- 精華
- 0
- 積分
- 2890
- 點名
- 0
- 作業系統
- 〔略〕
- 軟體版本
- 〔略〕
- 閱讀權限
- 100
- 性別
- 男
- 來自
- 〔略〕
- 註冊時間
- 2013-5-13
- 最後登錄
- 2024-11-21
|
25#
發表於 2021-12-13 16:41
| 只看該作者
若想100,200,500分別編碼:
Sub 流水編號()
Dim Arr, i&, P%, V&, N&, T1$, T2$, x%, TR, YR, SS&(3)
Arr = Range([c1], [b65536].End(xlUp)(1, 0))
TR = [{"A","F","B"}]
YR = [{64564,38000, 81140}]
For i = 3 To UBound(Arr)
If Arr(i, 1) = "合計" Then Exit For
N = N + 1: Arr(i - 2, 1) = ""
P = Val(Arr(i, 2)): V = Val(Arr(i, 3))
x = Switch(P = 100, 1, P = 200, 2, P = 500, 3, P = P, 0)
If P = 0 Or V = 0 Or x = 0 Then GoTo i01
T1 = TR(x) & Format(YR(x) + SS(x) + 1, "0000000")
T2 = TR(x) & Format(YR(x) + SS(x) + V, "0000000")
Arr(i - 2, 1) = T1 & IIf(T1 = T2, "", "-" & T2)
SS(x) = SS(x) + V
i01: Next i
[d3].Resize(N) = Arr
End Sub |
|
發表於 2021-12-13 12:24
| 
