Andy2483

¦^´_ 29# 198188


    ½Ð±N³W«h¤å¦r±Ô­zªí³æ¤Æ«á¤W¶Ç·s½d¨Ò ,¦p¤U¹Ï

¤U¸ü (219.95 KB)

«e¤Ñ 16:43

198188

¦^´_  198188


    ½Ð±N³W«h¤å¦r±Ô­zªí³æ¤Æ«á¤W¶Ç·s½d¨Ò ,¦p¤U¹Ï
Andy2483 µoªí©ó 2025-10-22 16:44

¤U¸ü (117.61 KB)

¬Q¤Ñ 08:04

¥Ñ©ó¤¤¤å·|¦³¶Ã½X¡A©Ò¥H§Ú¤W¸üªº½d¨Ò Result ¥Î­^¤å±Ô­z¡A¥t¥~¹Ï¤ù¥Î¤¤¤å±Ô­z¡Cªþ¥ó³W«h¬O¤¤¤å´y­z³W«h¡C

Rule Äæ A "½Æ»sªí®æ Copy Form"  ÂŦⳡ¤À¬O«ü¥Ü½ÆÂø­þ­Ó¼ÒªOªºªí®æ
Rule Äæ B - H "³Æª`²Å¸¹ Remark " ¶À¦â³¡¤À¬O¹ïÀ³ Part List Äæ G "³Æª`" & Frame per Dwg Äæ F "²Õ¸Ë¹Ïªº¥X³f±¡ªp" ªº¿ë»{
Rule Äæ K - AB " ÃöÁä¦rKey Word" ºñ¦â³¡¤À¬O ¹ïÀ³ Part List Äæ A "¥[¤u¥ó½s¸¹" & Frame per Dwg Äæ B "²Õ¸Ë¹Ï½s¸¹" ªºÀY¨â­Ó¦rªº¿ë»{
·í Rule Äæ B - H "³Æª`²Å¸¹ Remark " & Rule Äæ K - AB " ÃöÁä¦rKey Word" ¨â­Ó³£²Å¦X±ø¥óªº ®Ú¾ÚRule Äæ A  "½Æ»sªí®æ Copy Form" ¨Ó¥Í²£¤@­Ó·sªºªí®æ¡AµM«á«ö·Ó ³W«h ¨Ó¶ñ¤J¸ê®Æ¡C
³Ì«á§â©Ò¦³·s¥Í¦¨ªºªí®æ¡A¥´¦L¦¨PDF Àx¦s¦b·í¤Uªº®à­± / ¸ÓExcel ªº¦ì¸m¡C

Andy2483

¦^´_ 32# 198188


¤u§@ªí¦WºÙ¥u¯à¦³31­Ó¦r¤¸,­Y¶W¹L¦p¦ó³B²z?

198188

¦^´_  198188


¤u§@ªí¦WºÙ¥u¯à¦³31­Ó¦r¤¸,­Y¶W¹L¦p¦ó³B²z?
Andy2483 µoªí©ó 2025-10-23 08:43

¦pªG ¥u¦³1­Ó Key Word ªºª½±µ¥Î Key Word ©R¦W,
¦pªG¥X²{¶W¹L¤@­Ó key word ªºª½±µ¥ÎEnglish Product©R¦W¡C

Á|¨Ò
Hardware = HW
Screw = Screw
Weather Sealant = Weather Sealant
Flush Slab Edge Cover Frame = FF
Door Frame  = DF
Casting = Casting
Structural Sealant = Structural Sealant
Gasket = Gasket
Thermal Rock = TR
   
¥t¥~Remark
ª½±µ¥Î²Å¸¹©R¦W¡C
²Å¸¹ - ¥Î -
²Å¸¹ Y ¥ÎY
²Å¸¹ O ¥Î O
²Å¸¹ T ¥Î T
²Å¸¹ W ¥Î W

Andy2483

¦^´_ 34# 198188


    ¥H¤U½Ð¥ý¸Õ¸Õ¬Ý

Option Explicit
Sub TEST()
Dim Arr, Brr, Crr(1 To 10000, 1 To 18), A, V, Z, Q, S, i&, j%, R&, C%, y%, K, X%, T$, T1$, T2$, T3$, T4$, T5$, Rk$, W, L
Application.ScreenUpdating = False
Set Z = CreateObject("Scripting.Dictionary")
Brr = [Rule!A1].CurrentRegion
For j = 2 To 8
   T = Trim(Mid(Brr(2, j), 1, Len(Brr(2, j)) * 2 - LenB(StrConv(Brr(2, j), vbFromUnicode)) - 2))
   For i = 3 To UBound(Brr)
      If Brr(i, j) <> "" Then Z(Brr(i, j) & "|") = T: Exit For
   Next
Next
For i = 3 To UBound(Brr)
   For j = 11 To 28
      If Brr(i, j) = "" Then
         If j = 12 Then
            Z("/" & Brr(i, 9) & "/") = Brr(i, 11)
         End If
         Exit For
      End If
      Z("/" & Brr(i, j) & "/") = Brr(i, 9)
      Z("/" & Brr(i, j) & "//") = Brr(i, 10)
   Next
Next
For i = 3 To UBound(Brr)
      T = Brr(i, 9)
      Rk = ""
      For j = 2 To 8
         Rk = Rk & Brr(i, j)
      Next
      S = Brr(i, 1)
      Z(T & "\" & Rk) = S
      Z(S & "/UR") = Sheets(S).[A65536].End(3)(2).Row
      Z(S & "/UC") = Sheets(S).Cells(Z(S & "/UR") - 1, 256).End(xlToLeft).Column
Next
Arr = Sheets("Material").UsedRange
For i = 2 To UBound(Arr)
   Z(Arr(i, 3) & "/m") = i
Next
Brr = Range(Sheets("Part List").[M1], Sheets("Part List").[A65536].End(3))
For i = 2 To UBound(Brr)
   T5 = Left(Brr(i, 1), 2)
   T = Z("/" & T5 & "/")
   T4 = Z("/" & Left(Brr(i, 1), 2) & "//")
   Rk = Brr(i, 7)
   T2 = Z(T & "\" & Rk)
   T3 = T & "-" & Z(Rk & "|")
   If Len(T3) > 31 Then
      T3 = T4 & "-" & Z(Rk & "|")
      If Len(T3) > 31 Then
         T3 = T5 & "-" & Rk
      End If
   End If
   Z("|" & T3) = T2
   A = Z("/" & T3)
   R = Z("r/" & T3)
   If Not IsArray(A) Then
      A = Crr
   End If
   If T2 = "Bom" Then
      R = R + 1
      A(R, 1) = R
      A(R, 2) = Brr(i, 1)
      A(R, 3) = Brr(i, 12)
      If A(R, 3) <> "" Then
         A(R, 4) = Arr(Z(A(R, 3) & "/m"), 5)
         A(R, 5) = Arr(Z(A(R, 3) & "/m"), 6)
         A(R, 6) = Arr(Z(A(R, 3) & "/m"), 7)
         A(R, 8) = Arr(Z(A(R, 3) & "/m"), 11)
         A(R, 9) = Arr(Z(A(R, 3) & "/m"), 10)
         A(R, 13) = Arr(Z(A(R, 3) & "/m"), 8)
      End If
      A(R, 7) = Brr(i, 4) & " x " & Brr(i, 5)
      A(R, 10) = Brr(i, 3)
      A(R, 18) = Brr(i, 7)
      GoTo i01
   End If
   If T2 = "Gasket" Then
      R = R + 1
      A(R, 1) = R
      A(R, 2) = Brr(i, 1)
      A(R, 9) = Brr(i, 3)
      A(R, 6) = Brr(i, 5)
      A(R, 7) = Brr(i, 6)
      A(R, 11) = Brr(i, 7)
      A(R, 3) = Brr(i, 12)
      If A(R, 3) <> "" Then
         A(R, 4) = Arr(Z(A(R, 3) & "/m"), 5)
         A(R, 5) = Arr(Z(A(R, 3) & "/m"), 6)
         A(R, 8) = Arr(Z(A(R, 3) & "/m"), 8)
      End If
      GoTo i01
   End If
   If T2 = "Structural" Then
      R = R + 1
      A(R, 1) = R
      A(R, 2) = Brr(i, 1)
      A(R, 3) = Brr(i, 2)
      A(R, 5) = Brr(i, 3)
      L = Split(Brr(i, 2) & "mm", "mm")(0)
      W = Split(Brr(i, 2) & "mm", "mm")(1)
      L = Val(StrReverse(Mid(Val(StrReverse(L & 1)), 2)))
      W = Val(StrReverse(Mid(Val(StrReverse(W & 1)), 2)))
      A(R, 6) = L * W
      A(R, 8) = A(R, 5) * A(R, 6) / 1000
      A(R, 7) = Application.RoundUp(A(R, 5) * A(R, 6) / 1000, 0)
      GoTo i01
   End If
   If T2 = "DN Material" Then '
      R = R + 1
      A(R, 1) = R
      A(R, 2) = Brr(i, 1)
      A(R, 3) = Brr(i, 12)
      A(R, 4) = Brr(i, 5)
      A(R, 7) = Brr(i, 3)
      If A(R, 3) <> "" Then
         A(R, 5) = Arr(Z(A(R, 3) & "/m"), 11)
         A(R, 6) = Arr(Z(A(R, 3) & "/m"), 8)
         A(R, 6) = A(R, 5) * A(R, 7)
      End If
      A(R, 9) = Brr(i, 7)
      GoTo i01
   End If
   If T2 = "Fabrication Extrusion" Then
      R = R + 1
      A(R, 1) = R
      A(R, 2) = Brr(i, 1)
      A(R, 3) = Brr(i, 11)
      A(R, 4) = Brr(i, 10)
      A(R, 5) = Brr(i, 4)
      A(R, 6) = Brr(i, 3)
      A(R, 7) = Brr(i, 1)
      If A(R, 7) Like "*-*-*" Then
         Q = Split(A(R, 7), "-")
         A(R, 7) = Q(0) & "-" & Q(1)
      End If
      A(R, 8) = Brr(i, 7)
   End If
i01: Z("/" & T3) = A
   Z("r/" & T3) = R
Next
Brr = Range(Sheets("Frame per Dwg").[M1], Sheets("Frame per Dwg").[A65536].End(3))
For i = 2 To UBound(Brr)
   T = Z("/" & Left(Brr(i, 2), 2) & "/")
   T4 = Z("/" & T & "/")
   Rk = Brr(i, 6)
   T2 = Z(T & "\" & Rk)
   T3 = T4 & "-" & Z(Rk & "|")
   Z("|" & T3) = T2
   A = Z("/" & T3)
   R = Z("r/" & T3)
   If Not IsArray(A) Then
      A = Crr
   End If
   If T2 = "Finish" Then
      R = R + 1
      A(R, 1) = R
      A(R, 2) = Brr(i, 2)
      A(R, 3) = Brr(i, 3)
      A(R, 4) = Brr(i, 4)
      A(R, 5) = Brr(i, 5)
      A(R, 7) = Brr(i, 6)
   End If
   Z("/" & T3) = A
   Z("r/" & T3) = R
Next
Workbooks.Add
Q = ActiveWorkbook.Name
For Each K In Z.Keys
   If IsArray(Z(K)) And Z("r" & K) > 0 Then
      T = Mid(K, 2)
      ThisWorkbook.Sheets(Z("|" & T)).Copy Before:=Workbooks(Q).Sheets(1)
      ActiveSheet.Name = T
      With Cells(Z(Z("|" & T) & "/UR"), 1).Resize(Z("r" & K), Z(Z("|" & T) & "/UC"))
         .Value = Z(K)
         .Borders.LineStyle = xlContinuous
      End With
   End If
Next
End Sub

198188

¦^´_  198188


    ¥H¤U½Ð¥ý¸Õ¸Õ¬Ý

Option Explicit
Sub TEST()
Dim Arr, Brr, Crr(1 To 10000,  ...
Andy2483 µoªí©ó 2025-10-23 11:57

¤U¸ü (53.81 KB)

¬Q¤Ñ 13:09

°õ¦æ«á¡A¥X²{³o­Ó¿ù»~¡C

Andy2483

¦^´_ 36# 198188


    ¥i¯à­ì¦]:Materialªí¸ê®Æ¤£§¹¾ã
Part ListªíL(¨ÑÀ³°Ó½s¸¹) ¦b MaterialªíCÄæ§ä¤£¨ì  ¬ÛÀ³ªº(¨ÑÀ³°Ó½s¸¹)

198188

¦^´_  198188


    ¥i¯à­ì¦]:Materialªí¸ê®Æ¤£§¹¾ã
Part ListªíL(¨ÑÀ³°Ó½s¸¹) ¦b MaterialªíCÄæ§ä¤£ ...
Andy2483 µoªí©ó 2025-10-23 13:59

¨º¯à¤£¯à¥[¤@¥y¡A¦pªGŪ¨úMaterial ®É§ä¤£¨ìªº¡Aª½±µªÅ¥Õ¡H

Andy2483

¦^´_ 38# 198188


    ¸Ì­±¦³3¦æ:    If A(R, 3) <> "" Then
³£¸m´«¦¨:    If A(R, 3) <> "" And Z.Exists(A(R, 3) & "/m") Then

198188

¤U¸ü (114.45 KB)

¬Q¤Ñ 14:52

¦^´_  198188


    ¸Ì­±¦³3¦æ:    If A(R, 3)  "" Then
³£¸m´«¦¨:    If A(R, 3)  "" And Z.Exists( ...
Andy2483 µoªí©ó 2025-10-23 14:38

§ï§¹¤§«á¡A¹B¦æµ{¦¡¡A¾ã­ÓExcel ¤@ª½ ¨S¦³¦^À³¡C