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

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

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

ªþ¥ó¬O¤@­Ó¦h¤u§@­¶ªº¤¬¬Û©â¨ú¸ê®Æªº¼Ò¦¡¡AŪ¨ú¦Z²o¯A¤@¨Ç®Ø綫¡AªÅ®æ®æ¦¡µ¥°ÝÃD¡A½Ð¦U¤j¤jÀ°¦£¬Ý¬Ý¦³¨S¦³¤èªk¸Ñ¨M¡AÁÂÁ¡C

VBA FORM.rar (116.72 KB)

ªþ¥ó¬O¤@­Ó¦h¤u§@­¶ªº¤¬¬Û©â¨ú¸ê®Æªº¼Ò¦¡¡AŪ¨ú¦Z²o¯A¤@¨Ç®Ø綫¡AªÅ®æ®æ¦¡µ¥°ÝÃD¡A½Ð¦U¤j¤jÀ°¦£¬Ý¬Ý¦³ ...
198188 µoªí©ó 2024-2-6 11:19



   ªþ¤W©î¤À²M³æ®æ¦¡ºc«ä¡A§Æ±æ¯à°÷µ¹¦U¦ì¤j¤j§ó¥[®e©ö²z¸Ñ¡C

Detail Packing List Print Query.rar (12.04 KB)

TOP

¦^´_ 1# 198188


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,³o½d¨Ò¦nÃø,¦U¬q¸¨©ú²Óªº¦C¶¶§Ç§ä¤£¨ì³W«h,¾Ç²ß¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò

Option Explicit
Sub TEST()
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&
Dim PGNr, Rackr, Itemr, PGr, DPLs As Worksheet, xR As Range, xRs As Range, xRe As Range
Set Z = CreateObject("Scripting.Dictionary"): Application.ScreenUpdating = False
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]: xR.Resize(1000, 14).Borders.LineStyle = 1
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) & " ­«½Æ": 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ªí " & T5 & " ­«½Æ": 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) ': MsgBox Z(PGNr(i, 2) & "/GD")
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: 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
      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 xRe = 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)
         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: GoTo j01
         End If
         Cells(Y, 6) = Cells(Y, 6) + Val(Itemr(P(j), 7))
j01:  Next
      With Range(xRe, 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: Next
      End With
      Range(xRs, xR(1, 14)).Merge
   Next
   Set xR = xR(2)
A01: Next
Set xR = xR(2): xR(1, 5) = "TOTAL": xR(1, 8) = V8: xR(1, 9) = V9: xR(1, 10) = V10: xR(1, 10).Resize(1, 5).Merge
With xR.Resize(1, 14): .Font.Size = 12: .Font.Bold = True: End With
Set Z = Nothing: Erase PGNr, Rackr, Itemr, PGr
End Sub

¯¬¦U¦ì«e½ú ·s¬K´r§Ö
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

ªþ¤W©î¤À²M³æ®æ¦¡ºc«ä¡A§Æ±æ¯à°÷µ¹¦U¦ì¤j¤j§ó¥[®e©ö²z¸Ñ¡C
198188 µoªí©ó 2024-2-7 10:20



    ¦P½g°ÝÃD «Øij ¥Î­×§ï ©Î¬O ¦^ÂÐ.. ¤£µM«Ü¶Ã

  ¦h°µ¦h·Q¦h¾Ç²ß¡A¤Ö¬Ý¤Ö¿ù¤Ö°g³~

  ¦h°µ=¦h¦h½m²ß¡A¦h¦h½s¼g¡C
  ¦h·Q=·Q·Q¬°¤°»ò¤H®aµ{¦¡­n¨º¼Ë¼g¡A¦pªG´«¦¨¦Û¤v¡A¤S·|«ç¼g¡C
  ¦h¾Ç²ß=¾Ç²ß¤H®aªºµo°Ý¨Ã¸Ñµª¡A¾Ç²ß¤H®aªº¼gªk

  ¤Ö¬Ý=¥u¬Ý¤£°µ¤]ªPµM

TOP

¦^´_ 3# Andy2483


ÁÂÁ±zªº¸Ñµª¡A¸Õ¹Lµ²ªG¦Z¡A¦³ªþ¥óª`ÄÀ¡A¤è«K²M´·³W«h¡C
¥t¥~¥i§_¦bVBA¥[¤Wª`ÄÀ¡A¤è«K¾Ç²ß¡AÁÂÁ¡C

VBA FORM R01.rar (128.06 KB)

¥´¦LÃä®Ø³W«h.rar (76.94 KB)

TOP

¦^´_ 4# mark15jill


    ÁÂÁ¤j¤j´£¿ô¡C:lol

TOP

¦^´_ 5# 198188

¥ý¬Ý¬Ý°õ¦æ«á¬O§_¬°»Ý¨Dªº¦C¦L¤À­¶,¦A¬Ý¬Ý«áÄò¦p¦ó¸Ñ
20240219_4.zip (177.64 KB)
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 7# Andy2483


    ªþ¤W¡§VBA¥´¦L®æ¦¡¡¨¡A³o¬O®Ú¾Ú±zªºVBA °µ¥X¨Óªº®ÄªG¡A¥t¤@­Óªþ¥ó¡§¥¿½T®æ¦¡¡¨¬O§Ú·Q­nªº®ÄªG¡C
®Ú¾Ú®ÄªG¡A¦n¹³Âù¼Æªº¥u¯àÅã¥Ü¤@¦æ¡C

VBA ¥´¦L®æ¦¡.rar (526.5 KB)

¥¿½Tªº®æ¦¡.rar (486.09 KB)

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2024-2-19 15:27 ½s¿è

¦^´_ 8# 198188


    ³o¼Ë¦^´_µLªk½T»{¤À­¶¬O§_¥¿½T,¦pªG¦A½Õ¾ã¹LÄæ¼e©Î¤ñ¨Ò´N±o­«ºâ°Ñ¼Æ
¥H¤Uªþ¥ó¬O7#Àɮ׶ץXPDFÀɪºµ²ªG,»P8#¦^´_Âû¦PÀnÁ¿
20240219_4.zip (186.6 KB)

¥H¤U¬O«á¾Ç¤À­¶¹wÄýªºª¬ªp:
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 9# Andy2483

§Ú¥´¦LPDF Á`¬O§â³Ì«á¤@¦æ±À¨ì²Ä¤G­¶¡A³o­Ó¥i¯à¬O¤j®a¥´¦LªºÃä¬É¤£¤@¼Ë§a¡C


¥t¥~¦b¬Û¦PªºSRùØ­± ©M ¦b¥´¦L®É¦P¤@­¶¡A
Äæ C ¤ÎÄæE ¦pªG¬Û¦P¡A¥u­n²Ä¤@¦æ¦³¤å¦r¡A
Äæ C ²Ä¤@¦æ¦³®Ø綫¡A¤§«áªº©Ò¦³¬Û¦Pªº¡A¥u­n¥|Ã䦳®Ø綫
Äæ E ©Ò¦³¬Û¦Pªº¡A¥u­n¥|Ã䦳®Ø綫
ÄæH,I,J,K,L,M,N ¥|Ã䦳®Ø綫
¡]¤§«e¦n¹³¥i¥H¡A²{¦bÅܦ¨¨S¦³³o­Ó¥\¯à¤F¡^

TOP

        ÀR«ä¦Û¦b : ¥Í®ð¡A´N¬O®³§O¤Hªº¹L¿ù¨ÓÃg»@¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD