- 帖子
- 2834
- 主題
- 10
- 精華
- 0
- 積分
- 2890
- 點名
- 0
- 作業系統
- 〔略〕
- 軟體版本
- 〔略〕
- 閱讀權限
- 100
- 性別
- 男
- 來自
- 〔略〕
- 註冊時間
- 2013-5-13
- 最後登錄
- 2024-11-22
|
5#
發表於 2018-10-13 11:51
| 只看該作者
Sub 更新()
Dim Arr, Brr, C&, i&, T1$, T2$, U$, N&
Call 清除
Arr = Range([出貨及取貨!F1], [出貨及取貨!A1].Cells(Rows.Count, 1).End(xlUp))
ReDim Brr(1 To UBound(Arr), 1 To 11)
For i = 2 To UBound(Arr)
T1 = Arr(i, 1): If T1 = "" Then GoTo 101
If T1 = "OS" Then
If Arr(i, 6) <> "PRINT CUP" And Left(Arr(i + 1, 1), 2) <> "YP" Then T2 = "": GoTo 101
T2 = "P": N = N + 1
Brr(N, 1) = Arr(i - 1, 3): Brr(N, 2) = Arr(i - 1, 2): Brr(N, 3) = "#" & Arr(i - 1, 1)
End If
U = Left(T1, 1) & Right(T1, 1)
C = Switch(U = "Y9", 4, U = "Y1", 7, U = "Y7", 10, U = U, 0)
If T2 = "" Or C = 0 Then GoTo 101
Brr(N, C) = T1: Brr(N, C + 1) = Arr(i, 3) * IIf(Left(T1, 2) = "YP", -1, 1)
101: Next i
If N = 0 Then Exit Sub
With [出貨結餘!B7:L7].Resize(N)
.Value = Brr
.Columns(1).NumberFormatLocal = "m/d"
.Borders.LineStyle = 1
End With
End Sub
'======================================
Sub 清除()
Sheets("出貨結餘").UsedRange.Offset(6, 0).EntireRow.Delete
End Sub
 收集出貨及取回之數量.rar (42.93 KB)
|
|
發表於 2018-10-10 19:21
| 
