- 帖子
- 1447
- 主題
- 40
- 精華
- 0
- 積分
- 1471
- 點名
- 0
- 作業系統
- Windows 7
- 軟體版本
- Excel 2010 & 2016
- 閱讀權限
- 50
- 性別
- 男
- 來自
- 台灣
- 註冊時間
- 2020-7-15
- 最後登錄
- 2025-2-11
|
For Each A In Z.KEYS
'↑設逐項迴圈!令A變數是Z字典中的key
If Right(A, 3) <> "/RN" Then GoTo A01 Else Q = Split(Z(A), ","): xR = Z(Split(A, "/RN")(0) & "/GD")
'↑如果A變數右3字不是"/RN"字串!就令程序跳到標示A01位置繼續執行,
'否則就令Q變數是以逗號分割字串(以A變數查Z字典回傳的字串)所形成的一維陣列,令xR儲存格值是查Z字典回傳值
With xR.Resize(, 14): .Merge: .Font.Size = 9: .Font.Bold = True: .Rows.RowHeight = 52 * Sh: End With
'↑令CONTAINER NO標題格合併,文字大小9,設文字粗體,列高52個單位
For i = 1 To UBound(Q)
'↑設順迴圈!令i從1 到Q陣列最大索引號
Set xR = xR(2): xR.Resize(1, 14).Interior.ColorIndex = 15: xR.Resize(1, 14).Font.Bold = True: xR.Rows.RowHeight = 27 * Sh
'↑令xR儲存格變為下一格,令從xR儲存格右擴展14格範圍儲存格底色為灰色/文字為粗體,列高27個單位
With xR.Resize(1, 4): .Merge: .Font.Size = 12: .Value = "'" & Q(i): End With
'↑令從xR儲存格右擴展14格範圍儲存格合並/文字大小12/令文字內容為i迴圈Q陣列值(前面加單引號是令強制其為文字)
xR(1, 6).Resize(, 2).Merge: xR(1, 10).Resize(, 5).Merge
'↑令段落儲存格合併
P = Split(Z(Q(i) & "|"), ","): R = Val(P(0))
'↑令P變數是以逗號分割字典裡記錄的Item表列號串,令R變數是Rack表的列號
xR(1, 8) = Val(Rackr(R, 6)): xR(1, 9) = Val(Rackr(R, 7)): xR(1, 10) = Rackr(R, 8) & " x " & Rackr(R, 9) & " x " & Rackr(R, 10)
'↑令自xR儲存格右8格(H)儲存格值是R變數列6欄Rackr陣列值,令自xR儲存格右9格(I)儲存格值是R變數列9欄Rackr陣列值,
'令自xR儲存格右10格(H)儲存格值是R變數列8/9/10欄Rackr陣列值以"x"符號連接所組成的新字串
V8 = V8 + xR(1, 8): V9 = V9 + xR(1, 9): V10 = V10 + (Val(Rackr(R, 8)) * Val(Rackr(R, 9)) * Val(Rackr(R, 10)) / 10 ^ 9): Set xRs = xR(2, 1)
'↑令V8變數累加 自xR儲存格右8格(H)儲存格值,令V9變數累加 自xR儲存格右9格(H)儲存格值,令V10變數是長*寬*高值,令xRs變數是xR儲存格的下一格儲存格
For j = 1 To UBound(P)
'↑設順迴圈!令j變數從1 到P陣列最大索引號
Tp1 = Itemr(P(j), 5): Tp3 = Z(Itemr(P(j), 6) & "^"): Tp5 = Itemr(P(j), 4)
'↑令Tp1是 Item表Item Number值,令Tp3是以 Item表Item Description Group值對照GP表得到的 Description
Y = Z(Q(i) & "/" & Tp1 & "/" & Tp3 & "/" & Tp5)
'↑令Y變數是 5個關鍵字串以"/"符號連接起來的字串查Z字典回傳值
If Y = 0 Then
'↑如果Y變數是 0
Set xR = xR(2): xR.Resize(1, 14).Font.Size = 9: xR = Tp1: xR(1, 3) = Tp3: xR(1, 5) = Tp5: xR(1, 6) = Val(Itemr(P(j), 7))
'↑令xR儲存格變更為下一格儲存格,令從xR儲存格右擴展14格範圍儲存格文字大小9,令xR儲存格值是 Tp1變數值,令xR儲存格右3格值是 Tp3變數值,
'令xR儲存右5格值是 Tp5變數值,令xR儲存右6格值是 Item表的 Quantity值
Z(Q(i) & "/" & Tp1 & "/" & Tp3 & "/" & Tp5) = xR.Row: xR.Rows.RowHeight = 27 * Sh: GoTo j01
'↑令Y變數是 5個關鍵字串以"/"符號連接起來的字串當key,item是xR儲存格列號,令xR儲存格當列列高是27個單位,令跳到標示j01位置繼續執行
End If
Cells(Y, 6) = Cells(Y, 6) + Val(Itemr(P(j), 7))
'↑令Y變數列F欄儲存格值累加 Item表的 Quantity值
j01: Next
With Range(xRs, xR(1, 14))
'↑以下是關於xRs儲存格至 xR儲存格右14格之間範圍儲存格
.Sort KEY1:=.Item(6), Order1:=1, Header:=2: .Sort KEY1:=.Item(5), Order1:=1, Key2:=.Item(3), Order2:=1, Key3:=.Item(1), Order2:=1, Header:=2
'↑令該範圍儲存格做三層次無標題的順排序
For R = 1 To .Rows.Count: .Cells(R, 1).Resize(1, 2).Merge: .Cells(R, 3).Resize(1, 2).Merge: .Cells(R, 6).Resize(1, 2).Merge: .Cells(R, 8).Resize(1, 7).Merge: Next
'↑設順迴圈!令R變數從1 到該區域的列數,令各區段儲存格合併
End With
Next
Set xR = xR(2)
'↑令xR儲存格變更為下一格儲存格
A01: Next
Cells.VerticalAlignment = xlCenter: If ActiveSheet.VPageBreaks.Count + 1 = 2 Then ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
'↑令全部儲存格格式垂直方向文字置中,'↑如果列印範圍的橫向頁數是2! 就令縱向分頁線移至最右側,讓橫向頁數是1,自動調整其他配合列印參數
https://forum.twbts.com/viewthread.php?tid=19505
xR(1, 5) = "TOTAL": xR.Resize(, 4).Merge: xR(1, 5).Resize(, 3).Merge: xR(1, 8) = V8: xR(1, 9) = V9: xR(1, 10) = Round(V10, 2): xR(1, 10).Resize(1, 5).Merge: xR.Rows.RowHeight = 27 * Sh
'↑令xR儲存格右5格儲存格值是 "TOTAL" 字串,前4格範圍合併儲存格,右5格開始擴展右3格範圍合併儲存格,令填入統計值並且合併儲存格,令列高是27個單位
With xR.Resize(1, 14): .Font.Size = 12: .Font.Bold = True: End With
'↑令xR儲存格擴展右14格範圍文字大小為12 / 粗體
ActiveSheet.Names.Add Name:="PrintArea", RefersTo:=Range([A1], xR(, 14)): ActiveSheet.PageSetup.PrintArea = "PrintArea"
'↑令儲存格範圍建立名稱為 "PrintArea",令列印範圍為名稱 "PrintArea"
For i = 20 To xR.Row '800-327.3=472.7 : 472.7/327.3=1.44
'↑設順迴圈令i變數從20到 xR儲存格列號
TT = Cells(i, 3) & "/" & Cells(i, 5)
'↑令TT變數是i迴圈列C欄值與i迴圈列E欄值以"/"符號連接組成的新字串
If TT = "/" Then GoTo i03
'↑如果TT變數是 "/"符號!就令程序跳到標示i03位置繼續執行
If TT = SS Or SS = "" Then
'↑如果TT變數同SS變數 或SS變數是空字元??
If xRc Is Nothing Then Set xRc = Cells(i, 3): Set xRe = Cells(i, 5) Else Set xRc = Union(xRc, Cells(i, 3)): Set xRe = Union(xRe, Cells(i, 5))
'↑如果xRc儲存格是空的!就令xRc儲存格是i迴圈列C欄儲存格,令xRe儲存格是i迴圈列E欄儲存格,
'否則就令i迴圈列C欄儲存格納入xRc儲存格集之中,令i迴圈列E欄儲存格納入xRe儲存格集之中
End If
i03: RH = RH + Cells(i, 1).RowHeight
'↑令RH變數累加迴圈列高
If Cells(i + 1, 3) & "/" & Cells(i + 1, 5) <> TT Or RH / TH > Tts Then
'↑如果(i+1)迴圈列C欄值與(i+1)迴圈列E欄值以"/"符號連接組成的新字串不同於 TT變數或 RH變數除以TH變數的商 > Tts變數??
If Not xRc Is Nothing Then
'↑如果xRc儲存格有物件??
If xRc.Count > 1 Then With Intersect(xRc, xRc.Offset(1)): .Merge: .Value = "": End With: xRe.Merge: xRe.VerticalAlignment = xlTop: xRc.Offset(, 5).Merge
'↑如果xRc儲存格數大於1!就令xRc儲存格集區域第1列(不含)以下的儲存格做合併,令xRe儲存格集做合併,文字偏上
End If
Set xRc = Nothing: Set xRe = Nothing: SS = Cells(i + 1, 3) & "/" & Cells(i + 1, 5)
'↑令xRc xRe變數清空,令SS變數是 (i+1)迴圈列C欄值與(i+1)迴圈列E欄值以"/"符號連接組成的新字串
End If
If RH / TH > Tts Then Cells(i + 1, 1).PageBreak = xlPageBreakManual: RH = 0
'↑如果RH / TH商大於 Tts!就令在i+1列設定分頁線,令RH變數歸零
Next
Intersect(Range("PrintArea"), Range("PrintArea").Offset(19)).Borders.LineStyle = 1
'↑令表頭以外的資料格設定細框線
Set Z = Nothing: Erase PGNr, Rackr, Itemr, PGr: Set xR = Nothing: Set xRs = Nothing: Set xRc = Nothing: Set xRe = Nothing
End Sub |
|
發表於 2024-2-21 15:14
| 
