½Ð°Ý³W«h02F - 04F ¦pªG¦bData Base ¿z¿ï¦b³o­Ó½d³ò内ªº¬ÛÃö¸ê®Æ

198188

[attach]38095[/attach][attach]38096[/attach][attach]38097[/attach]

½Ð°Ý¦pªG³W«h¬O02F - 04F ©ÎªÌ¦h­Ó¼Ó¼h¡A¦pªG¦bData Base ¿z¿ï¦b³o­Ó½d³ò内ªº¬ÛÃö¸ê®Æ¡C
¦p¹Ï¤@¡A¹Ï¤G¡A¹Ï¤T
ªþ¤WExcel Data Base & ·Q­nªºµ²ªG¤Î³W«h¡C

198188

¤U¸ü (62.14 KB)

2025-10-5 16:19

¤U¸ü (202 KB)

2025-10-5 16:19

¤U¸ü (99.99 KB)

2025-10-5 16:19

198188

¤U¸ü (171.93 KB)

2025-10-6 11:37

B2=VLOOKUP($A2,'[Data Base.xlsx]WO No'!$AK,COLUMN(B1),)
hcm19522 µoªí©ó 2025-10-6 11:19

³o­Ó¦}«D§Úªº°ÝÃD­n¨D¡C
§Ú·N«ä¬O»¡¡A
§Ú¿é¤J02F-04F ¡]¹Ï1)
´N·|¦Û°Ê©â¨ú Layout Dwg ªíùØÄæ B ¼Ó¼hÄÝ©ó³o­Ó 02F - 04F ½d³ò内ªº¸ê®Æ¥X¨Ó ¡]¹Ï3¡^

§Ú¿é¤J03F-08F¡]¹Ï1)
´N·|¦Û°Ê©â¨ú Layout Dwg ªíùØÄæ B ¼Ó¼hÄÝ©ó³o­Ó 03F - 08F ½d³ò内ªº¸ê®Æ¥X¨Ó¡]¹Ï3¡^

198188

1. Sub Copy_Layout_To_Data() 'ok!
   
2.         Dim h, i, j As Integer
   
3.         Dim myString, myString1 As String
   
4.         Dim charToFind, charToFind1, charToFind2 As String
   
5.         Dim position, position1, position2, position3 As Long
   
6.         Dim stringLength, stringLength1 As Long
   
7.         j = 2
   
8.         
   
9.         h = Worksheets("Layout Dwg").Cells(Rows.Count, 1).End(3).Row
   
10.         h = h + 1
    
11.         
    
12.         myString = Sheets("Data").Range("C2")
    
13.         stringLength = Len(myString)
    
14.         charToFind = "-" ' Searching for lowercase '-' in "World"
    
15.         position = InStr(1, myString, charToFind, vbTextCompare)
    
16. 
    
17.         If position > 0 Then
    
18.         
    
19.         a = Mid(myString, 1, position - 2)
    
20.         b = Mid(myString, position + 1, stringLength - position - 1)
    
21.         
    
22.         For i = 2 To h
    
23.         Sheets("Layout Dwg").Select
    
24.         myString1 = Range("B" & i)
    
25.         charToFind2 = "F" ' Searching for lowercase 'F' in "World"
    
26.         position2 = InStr(1, myString1, charToFind2, vbTextCompare)
    
27.         
    
28.         If position2 > 0 Then
    
29.         c = Mid(myString1, 1, position2 - 1)
    
30.         If c = a Then
    
31.         Sheets("Layout Dwg").Select
    
32.         Range("A" & i & ":C" & i).Select
    
33.         Selection.Copy
    
34.         Sheets("Data").Select
    
35.         Range("M" & j).Select
    
36.         ActiveSheet.Paste
    
37.         j = j + 1
    
38.         Else
    
39.         If c > a And c <= b Then
    
40.         Sheets("Layout Dwg").Select
    
41.         Range("A" & i & ":C" & i).Select
    
42.         Selection.Copy
    
43.         Sheets("Data").Select
    
44.         Range("M" & j).Select
    
45.         ActiveSheet.Paste
    
46.         j = j + 1
    
47.         End If
    
48.         End If
    
49.         End If
    
50.         Next i
    
51.         
    
52.         Else
    
53.         charToFind1 = "&" ' Searching for lowercase '&' in "World"
    
54.         position1 = InStr(1, myString, charToFind1, vbTextCompare)
    
55.         If position1 > 0 Then
    
56.         Z = Mid(myString, 1, position1 - 1)
    
57.         y = Mid(myString, position1 + 1, stringLength - position1)
    
58.         
    
59.         For i = 2 To h
    
60.         
    
61.         Sheets("Layout Dwg").Select
    
62.         c = Range("B" & i)
    
63.         If c = Z Or c = y Then
    
64.         Sheets("Layout Dwg").Select
    
65.         Range("A" & i & ":C" & i).Select
    
66.         Selection.Copy
    
67.         Sheets("Data").Select
    
68.         Range("M" & j).Select
    
69.         ActiveSheet.Paste
    
70.         j = j + 1
    
71.         End If
    
72.         
    
73.         Next i
    
74.         
    
75.         Else
    
76.         W = myString
    
77.         For i = 2 To h
    
78.         
    
79.         Sheets("Layout Dwg").Select
    
80.         c = Range("B" & i)
    
81.         If c = W Then
    
82.         Sheets("Layout Dwg").Select
    
83.         Range("A" & i & ":C" & i).Select
    
84.         Selection.Copy
    
85.         Sheets("Data").Select
    
86.         Range("M" & j).Select
    
87.         ActiveSheet.Paste
    
88.         j = j + 1
    
89.         End If
    
90.         Next i
    
91.         
    
92.         End If
    
93.         End If
    
94.     End Sub

½Æ»s¥N½X

³o­Ó¦}«D§Úªº°ÝÃD­n¨D¡C
§Ú·N«ä¬O»¡¡A
§Ú¿é¤J02F-04F ¡]¹Ï1)
´N·|¦Û°Ê©â¨ú Layout Dwg ªíùØÄæ B ¼Ó¼h ...
198188 µoªí©ó 2025-10-6 11:36

¹Á¸Õ¥Î³o­Ó¤è¦¡¥i¥H°Ï§O¡A¦ý¬O¹B¦æ³t«×¤ÓºC¡A½Ð±Ð¦U¤j¤j¦³¨S¦³§ï¶iªÅ¶¡¡H

198188

¦^´_  198188


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò

...
Andy2483 µoªí©ó 2025-10-8 14:54

·PÁ«e½ú«üÂI

198188

¦^´_  198188


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò

...
Andy2483 µoªí©ó 2025-10-8 14:54

¤U¸ü (254.02 KB)

2025-10-9 17:27

«e½ú§A¦n¡AÀ˵ø«á¡Aµo²{¦³¨âÂIº|¤F´£¥X¡A
Layout Dwg  »Ý­n¥[¤@­Ó¿z¿ï±ø¥ó ¡§§å¦¸¡¨

Part List »Ý­n¼W¥[¤@­Ó¸ê®Æ¡G ¿z¿ï±ø¥ó¬O¡AWO-No ÄæF - ÄæK ªº¦U¼Ë¼Æ¾Ú¦r¥À¡AÁ|¨Ò WO-J057-022 ùØ­±¦³ "WS" ®Ú¾Ú Frame Per Dwg ùØ­±ªº²Õ¸Ë¹Ï¸¹§t¦³¡§WS¡¨¡A¦bPart List ¹ïÀ³²Õ¸Ë¸¹/¥[¤u¥ó¸¹ ¿z¿ï¥X¨Ó ¡]¥uÅã¥Ü¦U¼Ë¼Æ¾Ú¦³ªº¬ÛÃö¦r¥À¡^¼Æ¶q¤]¬O¨â­ÓªíªºÁ`¼Æ¬Û­¼ ¡]¦p¹Ï¤Îªþ¥ó¡^

198188

«e½ú§A¦n¡AÀ˵ø«á¡Aµo²{¦³¨âÂIº|¤F´£¥X¡A
Layout Dwg  »Ý­n¥[¤@­Ó¿z¿ï±ø¥ó ¡§§å¦¸¡¨

Part List ...
198188 µoªí©ó 2025-10-9 17:30

Layout Dwg §å¦¸°ÝÃD¤w¸g¸Ñ¨M¡C

Part List ¸É¥R¤è­± ÁÙ¨S¸Ñ¨M
Frame per Dwg ªí内ÄæB "²Õ¸Ë¹Ï¸¹" §t¦³ WO No ùØ­±ªº "¦U¼Ë¼Æ¾Ú" ÄæF - ÄæK ªº­^¤å¦r¥À¡AŪ¨ú ¦bPart List ÄæG "¹ïÀ³²Õ¸Ë¸¹/¥[¤u¥ó¸¹" ¬Û¦Pªº"²Õ¸Ë¹Ï¸¹"¡A¤Î¼Æ¶q¬Û­¼¡C

1. For i = 2 To UBound(Brr)
   
2.    If Z.Exists(Brr(i, 2)) Then
   
3.    If Brr(i, 4) = Sheets("Read").[A2] Then
   
4.       Z(Brr(i, 1)) = Z(Brr(i, 1)) + Val(Brr(i, 3))
   
5.       N = N + 1
   
6.       For j = 1 To 4: Brr(N, j) = Brr(i, j): Next
   
7.    End If
   
8.    End If
   
9. Next
   
10. If N > 0 Then Sheets("Layout Dwg").[A2].Resize(N, 4) = Brr: N = 0 Else MsgBox "Nothing under the floor": GoTo 12

½Æ»s¥N½X

198188

1. Set Z = CreateObject("Scripting.Dictionary")
   
2. 
   
3. xFile = "Data Base.xlsx"
   
4. 
   
5. Set xBook = Workbooks(xFile)
   
6. 
   
7. 
   
8. 
   
9. a = Sheets("WO No").Range("F2")
   
10. b = Sheets("WO No").Range("G2")
    
11. c = Sheets("WO No").Range("H2")
    
12. d = Sheets("WO No").Range("I2")
    
13. e = Sheets("WO No").Range("J2")
    
14. f = Sheets("WO No").Range("K2")
    
15. 
    
16. 
    
17. G = Sheets("Part List").Range("A1").CurrentRegion.Rows.Count + 1
    
18. 
    
19. Brr = Sheets("Frame per Dwg").[A1].CurrentRegion
    
20.   For i = 2 To UBound(Brr)
    
21.   If Mid(Brr(i, 2), 1, 2) = a Or Mid(Brr(i, 2), 1, 2) = b Or Mid(Brr(i, 2), 1, 2) = c Or Mid(Brr(i, 2), 1, 2) = d Or Mid(Brr(i, 2), 1, 2) = e Or Mid(Brr(i, 2), 1, 2) = f Then
    
22.    Z(Brr(i, 2)) = Z(Brr(i, 2))
    
23.       N = N + 1
    
24.       For j = 1 To 6: Brr(N, j) = Brr(i, j): Next
    
25.    End If
    
26. 
    
27. Next
    
28. 
    
29. N = 1
    
30. Arr = xBook.Sheets("Part List").[A1].CurrentRegion
    
31. For i = 2 To UBound(Arr)
    
32.    If Z.Exists(Arr(i, 7)) Then
    
33.       N = N + 1
    
34.       For j = 1 To 13
    
35.       Arr(N, j) = Arr(i, j)
    
36.       Next j
    
37.       Arr(N, 5) = Arr(N, 5) * Z(Arr(i, 7))
    
38.    End If
    
39. Next
    
40. If N > 0 Then Sheets("Part List").Range("A" & G).Resize(N, 13) = Arr

½Æ»s¥N½X

Layout Dwg §å¦¸°ÝÃD¤w¸g¸Ñ¨M¡C

Part List ¸É¥R¤è­± ÁÙ¨S¸Ñ¨M
Frame per Dwg ªí内ÄæB "²Õ¸Ë¹Ï ...
198188 µoªí©ó 2025-10-10 12:12

Part List ¸É¥R¤è­±¤]¸Ñ¨M¡A¦ý¬OµLªk®M¤J¬Û¦P¼Ò²Õ¡A»Ý­n¿W¥ß¶}¤@­Ó¼Ò²Õ¡CÁÙ¦³½Æ»s®É¡AÁ`¬O·|½ÆÂø²Ä¤@Ä檺¦W¥Ø¡C

198188

¦^´_  198188


¥H¤U¤è®×½Ð¸Õ¸Õ¬Ý

Option Explicit
Sub TEST_2()
Dim Brr, Z, Q, i&, j%, N&, T$ ...
Andy2483 µoªí©ó 2025-10-13 11:03

·PÁ«e½ú«üÂI¡C
³o­Ó¤è®×¨ú®ø¤F¤@­Ó¤§«eªº³W«h¡A Layout Dwg ùØ­±ªº¤À§G¹Ï¦b Frame Per Dwg §ä¤£¨ìªº¡A³o¨Ç¤À§G¹Ï¡]Layout Dwg ) ´N¦bPart List ùØ­±´M§ä¡A¨Ã¥X²{¦bPart List.

198188

¦^´_  198188


¥H¤U¤è®×½Ð¸Õ¸Õ¬Ý

Option Explicit
Sub TEST_2()
Dim Brr, Z, Q, i&, j%, N&, T$ ...
Andy2483 µoªí©ó 2025-10-13 11:03

¤U¸ü (113.05 KB)

2025-10-13 11:31

­è­è´ú¸Õ¤F¡A¾É¤£¥X¼Æ¾Ú¡C
®Ú¾ÚWO No ªº¦U¼Ë¼Æ¾Ú¡A¦ÓFrame per Dwg ùتº²Õ¸Ë¹Ï¸¹¦³³o­Ó¼Æ¾Úªº­^¤å¦r¥À¡A¦bPart List ¾É¥X¼Æ¾Ú¡A