- ©«¤l
- 1446
- ¥DÃD
- 40
- ºëµØ
- 0
- ¿n¤À
- 1470
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Excel 2010 & 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2020-7-15
- ³Ì«áµn¿ý
- 2024-10-21
|
¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-1-5 08:00 ½s¿è
¦^´_ 4# ã´£³¡ªL
ÁÂÁ«e½ú,¤£³Ó·P¿E
Option Explicit
Sub TEST_A1()
Dim Brr(2), N(2), i&, Arr, SS, S As Worksheet, T$, YM$, k%, j%
'¡ô«Å§iÅܼÆ:Brr¬O¤@ºû°}¦CBrr(0)~Brr(2),N¬O¤@ºû°}¦CN(0)~N(2),
'(Arr,SS)¬O³q¥Î«¬ÅܼÆ,i¬Oªø¾ã¼Æ,S¬O¤u§@ªíÅܼÆ,(T,YM)¬O¦r¦êÅܼÆ,
'(k,j)¬Oµu¾ã¼Æ
YM = Format("2022/1/22", "yyyy-m")
'¡ô¥OYM³o¦r¦êÅܼƬO (¤é´ÁÂର4½X¦~¤À³s±µ"-",¦A³s±µ¤ë¥÷)ªº¦r¦ê
ReDim Arr(1 To 20000, 1 To 11)
'¡ô«Å§iArr³o¤Gºû°}¦C½d³ò:Áa¦V±q1¨ì20000¦C¸¹,¾î¦V±q1¨ì11Ä渹
Brr(1) = Arr
'¡ô¥O¯Á¤Þ¸¹1ªºBrr°}¦CȬO Arr¤Gºû°}¦C
Brr(2) = Arr
'¡ô¥O¯Á¤Þ¸¹2ªºBrr°}¦CȬO Arr¤Gºû°}¦C
For Each S In Sheets
'¡ô³]Each°j°é,¥OS¬O°j°é¤u§@ªí
T = UCase(S.Name)
'¡ô¥OT³o¦r¦êÅܼƬO S°j°é¤u§@ªí¦W¸g¦r¤¸Âà¤j¼gªº·s¦r¦ê
k = Switch(T Like "QC#*", 1, T Like "CLS-QC#*", 2, T = T, 0)
'¡ô¥Ok³oµu¾ã¼Æ¬O Switch ¨ç¼Æ¦^¶ÇªºÈ,³W«h¦p¤U:
'¦pªG T¦r¦êÅܼƬO "QC"¶}ÀY,³s±µ¦Ü¤Ö±a¦³1½X¼Æ¦rªº³W«h,´N¦^¶Ç¼Æ¦r 1 µ¹kÅܼÆ
'¦pªG T¦r¦êÅܼƬO "CLS-QC"¶}ÀY,³s±µ¦Ü¤Ö±a¦³1½X¼Æ¦rªº³W«h,´N¦^¶Ç¼Æ¦r 2 µ¹kÅܼÆ
'¦pªG T¦r¦êÅܼƬO ¦Û¨µ¥¦¡,´N¦^¶Ç¼Æ¦r 0 µ¹kÅܼÆ
'https://learn.microsoft.com/zh-tw/office/vba/language/reference/user-interface-help/switch-function
If k = 0 Then GoTo s99
'¡ô¦pªGkÅܼƬO 0,´N¸õ¨ì s99¦ì¸mÄ~Äò°õ¦æ
Arr = S.Range("a1").CurrentRegion
'¡ô¥OArr³o¤Gºû°}¦C! ˤJ S°j°é¤u§@ªí[A1]¬Û¾FÀx¦s®æ¦ê¨Ã«áÂX®i¦¨ªº³Ì¤p¤è¥¿½d³òÀx¦s®æ¶°È
For i = 2 To UBound(Arr)
'¡ô³]¶¶°j°é!i±q2¨ìArr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹¼Æ
If Arr(i, 5) = 0 Then Exit For
'¡ô¦pªGi°j°é¦C5ÄæArr°}¦CȬO 0,´N¸õ¥Xi¼hFor°éÄ~Äò°õ¦æ
If Arr(i, 11) = YM Then
'¡ô¦pªGi°j°é¦C11ÄæArr°}¦CȬO YM¦r¦êÅܼÆ?
N(k) = N(k) + 1
'¡ô¥OkÅܼƯÁ¤Þ¸¹ªºN°}¦CȬO ¦Û¨²Ö¥[ 1
For j = 1 To UBound(Arr, 2)
'¡ô³]¶¶°j°é!j±q1¨ìArr°}¦C¾î¦V³Ì¤j¯Á¤ÞÄ渹¼Æ
Brr(k)(N(k), j) = Arr(i, j)
'¡ô¥OkÅܼƯÁ¤Þ¸¹Brr°}¦CÈ(¤Gºû°}¦C)¤¤ ,
'(kÅܼƯÁ¤Þ¸¹ªºN°}¦CÈ ¦C¸¹,j°j°éÄ渹),
'²Ä¤@¦¸»{Ãѳo¼Ëªº°}¦C,³o¤£ª¾¹D¬O¤£¬O©Ò¿×ªº¤Tºû°}¦C??ÁÂÁÂ
'³o¤Tºû°}¦CȬO i°j°é¦Cj°j°éÄæArr°}¦CÈ
Next j
End If
Next i
s99: Next
Set SS = Sheets(Array("QC Summary", "CLS Summary"))
'¡ô¥OSS³o³q¥Î«¬ÅܼƬO¤u§@ªí¶°
For k = 1 To 2
'¡ô³]¶¶°j°é!k±q1¨ì2
SS(k).UsedRange.Offset(1, 0).EntireRow.Delete
'¡ô¥OSSÅܼƤu§@ªí¶°k¯Á¤Þ¸¹¤u§@ªí,¨Ï¥ÎªºÀx¦s®æÂX®i³Ì¤p¤è¥¿Àx¦s®æ¶°,
'¦V¤U°¾²¾¤@¦CªºÀx¦s®æ¶°½d³ò¦C§R°£
If N(k) > 0 Then SS(k).[a2].Resize(N(k), 11) = Brr(k)
'¡ô¦pªGkÅܼƯÁ¤Þ¸¹ªºN°}¦CÈ >0 ,´N¥OSSÅܼƤu§@ªí¶°k¯Á¤Þ¸¹¤u§@ªí,
'[a2]ÂX®i¦V¤U kÅܼƯÁ¤Þ¸¹ªºN°}¦CȦC,¦V¥kÂX®i11Äæ,³o½d³òÀx¦s®æ,
'¥HBrr¤Tºû°}¦Cªº²Äk¯Á¤Þ¸¹¼h°}¦C±a¤J,ÁÂÁÂ
Next k
End Sub |
|