ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

Ū¨ú¤ÎÂkÃþ¤Î®Ø綫®æ¦¡

¦^´_ 30# 198188

ÁÂÁ«e½ú¦^´_
¼W¥[ Àˬd"PGN"ªí¸ê®Æ¬O§_¦³ Invoice No,°õ¦æµ²ªG


For i = 2 To UBound(PGNr)
   Z(PGNr(i, 1)) = "/" '20240226·s¼W
   If PGNr(i, 1) <> T Then GoTo i02
   If Z.Exists(PGNr(i, 2) & "/GD") Then
      Z(PGNr(i, 2) & "/GD") = "CONTAINER NO.:" & Mid(Z(PGNr(i, 2) & "/GD"), 2) & vbCrLf & PGNr(i, 3)
   End If
i02: Next
If Z(T) <> "/" Then MsgBox "PGNªí¨S¦³ " & T & " ¶µ¥Ø": Exit Sub   '20240226·s¼W
For i = 2 To UBound(Itemr)
   If Z.Exists(Itemr(i, 3) & "|") Then Z(Itemr(i, 3) & "|") = Z(Itemr(i, 3) & "|") & "," & i
Next
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 31# Andy2483


    ¥[¦b­þ­Ó¦ì¸m¡H³Ì«á¶Ü¡H

TOP

¦^´_ 31# Andy2483
¤§«e¤W¸ü¿ù¤Fªþ¥ó¡A³o­Óªþ¥ó¬O¹B¦æ¦Z³B²zªº®ÄªG¡C

VBA ¹B¦æ.rar (151.22 KB)

TOP

¦^´_ 33# 198188


    PGNªí¸Ì´N¬O¨S¦³ C864-2024 ³o¸ê®Æ,¤£ª¾©Ò¤ª
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 34# Andy2483


    ¤£¦n·N«ä¡A¥[¤F¡A¥ª¤W¨¤°ÝÃD¤£¦s¦b¡A¥k¤U¤èTOTAL¨º­Ó¦a¤èªº°ÝÃDÁÙ¬O¦s¦b¡C

VBA ¹B¦æ.rar (151.92 KB)

TOP

¦^´_ 30# 198188


    «Å§i¸Ìªº V8&, V9& §ï¬° V8#, V9#
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 36# Andy2483


    Dim Brr(1 To 100, 1 To 14), A, Z, Q, P, i&, j%, R&, C%, N&, x%, T$, T14$, T15$, T5$, V8#, V9#, V10#, Tp1$, Tp3$, Tp5$, Y&, SS$, Tts#
­×§ï¤F¡AÁÙ¦³¬õ¦â¦r¨â³B°ÝÃD¡C

TOP

¦^´_ 37# 198188

HÄæ¾ãÄæ¿ï¨ú¥kÁä >Àx¦s®æ®æ¦¡:


¦Ü©ó¦X­p10,½Ð¦Û¤v¸Õ¸Õ,27#¦³µù¸Ñ,¤@°_¾Ç²ß
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 38# Andy2483

¦X­p10¸Ñ¨M¤F¡A¤£¹L889´Nºâ¦b¥~­±Àx¦s®æ½Õ®Õ¤F®æ¦¡¡A¦ý¬O¹B¦æVBA¦Z¡AÁÙ¬O¤@¼ËÅܦ¨¾ã¼Æ¡C

    Sub TEST()
Dim Brr(1 To 100, 1 To 14), W, A, Z, Q, P, i&, j%, R&, C%, N&, x%, T$, T14$, T15$, T5$, V6#, V8#, V9#, V10#, Tp1$, Tp3$, Tp5$, Y&, SS$, Tts#
Dim PGNr, Rackr, Itemr, PGr, DPLs As Worksheet, xR As Range, xRs As Range, TT$, TH#, ii, RH, Sh#, xRc As Range, xRe As Range
Set Z = CreateObject("Scripting.Dictionary"): Application.ScreenUpdating = False: Application.DisplayAlerts = False: Sh = Rows(17).RowHeight / 32
Tts = [O1]: PGNr = [PGN!A1].CurrentRegion: Rackr = [Rack!A1].CurrentRegion: Itemr = [Item!A1].CurrentRegion: PGr = [PG!A1].CurrentRegion
Set DPLs = Sheets("DPL"): DPLs.Activate: T = [J3]: DPLs.UsedRange.Offset(19).EntireRow.Delete: Set xR = [A20]
Cells.VerticalAlignment = xlCenter: ActiveSheet.ResetAllPageBreaks: [A1].Activate: For i = 1 To 19: TH = TH + Rows(i).RowHeight: Next
For i = 2 To UBound(PGr)
   If Z(PGr(i, 1) & "^") = "" Then Z(PGr(i, 1) & "^") = PGr(i, 2) Else MsgBox "PGªí " & PGr(i, 1) & "REPEAT": Exit Sub
Next
For i = 2 To UBound(Rackr)
   If Rackr(i, 11) <> T Then GoTo i01
   T14 = Rackr(i, 14): T5 = Rackr(i, 5): T15 = Rackr(i, 15)
   If Not Z.Exists(T15) Then R = N Mod 3 + 1: C = N \ 3: N = N + 1: Brr(R, Array(1, 3, 4)(C)) = T15: Z(T15) = ""
   If InStr("," & Z(T14 & "/GD") & ",", "," & T15 & ",") = 0 Then Z(T14 & "/GD") = Z(T14 & "/GD") & "," & T15
   If InStr("," & Z(T14 & "/RN") & ",", "," & T5 & ",") = 0 Then Z(T14 & "/RN") = Z(T14 & "/RN") & "," & T5: Z(T5 & "|") = i Else MsgBox "Rack Sheet" & T5 & "REPEAT": Exit Sub
i01: Next
DPLs.[G12].Resize(3, 4) = Brr: DPLs.[G15] = "TOTAL " & N & " X 45'HC CONTAINER": N = 0
For i = 2 To UBound(PGNr)
   If PGNr(i, 1) <> T Then GoTo i02
   If Z.Exists(PGNr(i, 2) & "/GD") Then Z(PGNr(i, 2) & "/GD") = "CONTAINER NO.:" & Mid(Z(PGNr(i, 2) & "/GD"), 2) & vbCrLf & PGNr(i, 3)
i02: Next
For i = 2 To UBound(Itemr)
   If Z.Exists(Itemr(i, 3) & "|") Then Z(Itemr(i, 3) & "|") = Z(Itemr(i, 3) & "|") & "," & i
   
Next
For Each A In Z.KEYS
   If Right(A, 3) <> "/RN" Then GoTo A01 Else Q = Split(Z(A), ","): xR = Z(Split(A, "/RN")(0) & "/GD")
   With xR.Resize(, 14): .Merge: .Font.Size = 9: .Font.Bold = True: .Rows.RowHeight = 52 * Sh: End With
   For i = 1 To UBound(Q)
      Set xR = xR(2): xR.Resize(1, 14).Interior.ColorIndex = 15: xR.Resize(1, 14).Font.Bold = True: xR.Rows.RowHeight = 27 * Sh
      With xR.Resize(1, 4): .Merge: .Font.Size = 12:  .Value = "'" & Q(i): End With
      xR(1, 6).Resize(, 2).Merge: xR(1, 10).Resize(, 5).Merge
      P = Split(Z(Q(i) & "|"), ","): R = Val(P(0))
      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)
      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, 8): Set xRs = xR(2, 1)
      For j = 1 To UBound(P)
         Tp1 = Itemr(P(j), 5): Tp3 = Z(Itemr(P(j), 6) & "^"): Tp5 = Itemr(P(j), 4): W = W + Itemr(P(j), 7)
         Y = Z(Q(i) & "/" & Tp1 & "/" & Tp3 & "/" & Tp5)
         If Y = 0 Then
            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))
            Z(Q(i) & "/" & Tp1 & "/" & Tp3 & "/" & Tp5) = xR.Row: xR.Rows.RowHeight = 27 * Sh: GoTo j01
         End If
         Cells(Y, 6) = Cells(Y, 6) + Val(Itemr(P(j), 7))
j01:  Next
      With Range(xRs, xR(1, 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
      End With
   Next
   Set xR = xR(2)
A01: Next
Cells.VerticalAlignment = xlCenter: If ActiveSheet.VPageBreaks.Count + 1 = 2 Then ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
xR(1, 5) = "TOTAL:": xR(1, 6) = W: xR.Resize(, 4).Merge: xR(1, 6).Resize(, 2).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
With xR.Resize(1, 14): .Font.Size = 12: .Font.Bold = True: End With
ActiveSheet.Names.Add Name:="PrintArea", RefersTo:=Range([A1], xR(, 14)): ActiveSheet.PageSetup.PrintArea = "PrintArea"
For i = 20 To xR.Row '800-327.3=472.7 : 472.7/327.3=1.44
   TT = Cells(i, 3) & "/" & Cells(i, 5)
   If TT = "/" Then GoTo i03
   If TT = SS Or SS = "" Then
      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))
   End If
i03: RH = RH + Cells(i, 1).RowHeight
   If Cells(i + 1, 3) & "/" & Cells(i + 1, 5) <> TT Or RH / TH > Tts Then
      If Not xRc Is Nothing Then
         If xRc.Count > 1 Then With Intersect(xRc, xRc.Offset(1)): .Merge: .Value = "": End With: xRe.Merge: xRe.VerticalAlignment = xlTop: xRc.Offset(, 5).Merge
      End If
      Set xRc = Nothing: Set xRe = Nothing: SS = Cells(i + 1, 3) & "/" & Cells(i + 1, 5)
   End If
   If RH / TH > Tts Then Cells(i + 1, 1).PageBreak = xlPageBreakManual: RH = 0
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

TOP

¦^´_ 39# 198188

¼F®`,¥H¤U¬õ¦r¤]­n¥[,¥t¥~§ÚªºHÄæ¾ãÄæ(³s¦P«á¤èªºªÅ®æ)¿ï¨úÅܧó®æ¦¡¬°¤p¼Æ¨â¦ì«á,°õ¦æ´N·|±a¤p¼Æ889.00,½Ð¦A¸Õ¸Õ
For j = 1 To UBound(P)
         Tp1 = Itemr(P(j), 5): Tp3 = Z(Itemr(P(j), 6) & "^"): Tp5 = Itemr(P(j), 4): W = W + Itemr(P(j), 7)
         Y = Z(Q(i) & "/" & Tp1 & "/" & Tp3 & "/" & Tp5)
         If Y = 0 Then
            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))
            Z(Q(i) & "/" & Tp1 & "/" & Tp3 & "/" & Tp5) = xR.Row: xR.Rows.RowHeight = 27 * Sh: GoTo j01
         End If
         Cells(Y, 6) = Cells(Y, 6) + Val(Itemr(P(j), 7)): W = W + Itemr(P(j), 7)
j01:  Next
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : §Ú­Ì³Ì¤jªº¼Ä¤H¤£¬O§O¤H¡D¥i¯à¬O¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD