filter«¬ºA¤£²Å¦X

ssooi

§Ú¥ý±N¦U­Ó¦aÂI°µ¦¨¤@­Ó°}¦C
µM«á±NSheet1¸Ì­±ªº¦aÂI»P°}¦C°µ¤ñ¹ï
­Y²Å¦X¸Ó¦aÂI¡A«h±N¸Ó¦aÂI¾ã¦Cªº¸ê®Æ½Æ»s¨ì¸Ó¦aÂIªº¤À­¶
¦ý§Ú°µ¨ì¤@¥b´N¥d¦í¤F
¿ù»~°T®§¬O«¬ºA¤£²Å¦X

book1.zip (8.43 KB)

book
¤U¸ü¦¸¼Æ: 9

2020-8-1 00:08

code.zip (378 Bytes)

code
¤U¸ü¦¸¼Æ: 8

2020-8-1 00:08

n7822123

¦^´_ 1# ssooi


¦]¬°§AªºFilter ¥Îªk¿ù°Õ~~

²Ä1­Ó°Ñ¼Æ­n©ñ¤J "°}¦C"¡A²Ä2­Ó°Ñ¼Æ­n©ñ¤J "¦r¦ê"

ªì¾ÇªÌ½Ðµ½¥Î "F1" ¨Ó¬d¥Îªk

¤U¸ü (14.88 KB)

2020-8-1 02:43

©Ò¥H¨Ì§Aªºµ{¦¡  Filter(a,all(i)) ­n§ï¦¨ Filter(all,a)

­è¦n¦³­Ó½d¨Ò¥i¥H¥ÎFilter¥Ü½dµ¹§A¬Ý¡A¦p¤UÃìµ²

http://forum.twbts.com/thread-8370-1-1.html

¤£¹L§Aªºµ{¦¡¤£¥u³o­Ó°ÝÃD......

¦³¤£¤Ö»yªk¿ù»~¡AÁÙ¦³ÅÞ¿è°ÝÃD......

§AªºIF¥i¯à¥Ã»·¶i¤£¥h......¶i¥h¤F¤]·|°õ¦æ¥¢±Ñ

¦A¥[ªo§a!

­ã´£³¡ªL

°ò¥»¾Þ§@¥\¯à--¿z¿ï, À³¥i³B²z³o­Ó°ÝÃD,
¥ý¨Ï¥Î¿ý»s¨ú±oµ{¦¡½X...¬ã¨s¤U...

¦³°ÝÃD¦A´£~~

ssooi

¦U¦ì°ª¤â¡A¯uªººÉ¤O¤F~filter¤@ª½·d¤£©w¡A¥Î¥t¥~¤@ºØ¼gªk¡A¼g¨ì³o¸Ì¡A¤£ª¾¹D°ÝÃD¦b­þ¡AµLªk±N¾ã¦C½Æ»s¨ì«ü©w¤u§@ªíªº³Ì«á¤@Äæ¡AÁÂÁ¦U¦ì°ª¤â

1. Sub °j°é1()
   
2. 
   
3. All = Array("¤T°Ó", "»·ªF")
   
4. 
   
5. For r = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
   
6.    
   
7.     For i = 0 To UBound(All)
   
8.    
   
9.         If Cells(r, 2) = All(i) Then
   
10.             Rows(r).EntireRow.Copy
    
11.             Sheets(All).Select
    
12.             ActiveSheet.Paste
    
13.             Exit For
    
14.         End If
    
15.         
    
16.     Next
    
17. Next
    
18. 
    
19. End Sub

½Æ»s¥N½X

°a¤ªºµ

1. §Ú¤]¬O·s¤â¶¶«K½m²ß¤@¤U ¤£¹L³o¬q¦n¹³­þ¸Ì©Ç©Çªº  ¦ý§Ú¬Ý¤£¥X¨Ó ½Ð¤j¤j­Ì À°À°¦£ ¸Ó¦p¦ó­×§ï ·Pı ¦n¹³°j°é ¤Ó¦h ¤£¥²­nªº¥N½X ¦n¹³«Ü¦h 0.0
   
2. Public Sub ½m²ß¸ó¤u§@ªí°}¦C½m²ß()
   
3. 
   
4.     For x = 2 To Sheets.Count
   
5.         Sheets(x).Cells.Clear
   
6.         Sheets(1).Rows(1).Copy Sheets(x).Rows(1)
   
7.     Next x
   
8.    
   
9.     E = 2
   
10.     A = Array("¤T°Ó", "»·ªF", "«H¸q")
    
11.     K = Range(Cells(2, 1), Cells(2, 3).End(xlDown))
    
12.     ReDim B(LBound(K) To UBound(K))
    
13.     ReDim Preserve B(LBound(K) To UBound(K))
    
14. 
    
15.     For x = 2 To Sheets.Count
    
16.         For J = LBound(K) To UBound(K)
    
17.             For I = LBound(A) To UBound(A)
    
18.             If A(I) <> K(J, 2) Then E = 2
    
19.                 If A(I) = K(J, 2) Then
    
20.                 If Sheets(K(J, 2)).Cells(E, 1) <> "" Then E = E + 1
    
21.                     Sheets(K(J, 2)).Cells(E, 1) = K(J, 1)
    
22.                     Sheets(K(J, 2)).Cells(E, 2) = K(J, 2)
    
23.                     Sheets(K(J, 2)).Cells(E, 3) = K(J, 3)
    
24.                     E = E + 1
    
25.                 End If
    
26.             Next I
    
27.         Next J
    
28.     Exit For
    
29.     Next x
    
30.    
    
31.     Erase A, K, B
    
32.    
    
33. End Sub

½Æ»s¥N½X

°a¤ªºµ

­è¤~¸ÕµÛ§â  For x = 2 To Sheets.Count °j°é§R°£ µ²ªGµo²{ ®Ú¥»¥Î¤£¨ì ........XD

°a¤ªºµ

ReDim B(LBound(K) To UBound(K))
ReDim Preserve B(LBound(K) To UBound(K))
Erase B

³o¬q¤]¤£¥Î ....XD

ssooi

¦^´_ 5# °a¤ªºµ


ÁÂÁ½Ѧ찪¤â
§Ú¦¨¥\¤F
ÁÂÁÂ!

°a¤ªºµ

¦^´_ 8# ssooi


    E = E + 1 ³o¬q¤]¤£¥Î .....XD  ©êºp §Ú¤]¬O·s¤â§Ú­Ì¥i¥H¤¬¬Û¾Ç²ß

n7822123

¦^´_ 8# ssooi

®¥³ß®¥³ß¡A¤j·§¯à²q¥X§A­Ì¬O¥Î2­Ó°j°é°µ¤ñ¹ïªº¼gªk~

¤âÄo¤F¤@¤U¡A§Ú¤]¤½§G§Úªº¼gªk§a¡A¥u¦³"¤@­Ó°j°é"

¦pªG¥Î·Ç¤j´£¨ìªº ¤u§@ªí"¿z¿ï"¥\¯à¡AÀ³¸Ó·|¼gªº§ó²³æ

¨C­Ó¤À­¶¡A§Ú¦Û¤v¦h½Æ»s¤F"¼ÐÃD¦C"¡A³o¼Ë·Pı¤ñ¸û¥¿±`~

³o­ÓÃD¥Ø¥ÎG¤j³ßÅw¥ÎªºMatch¨ç¼Æ´N¥i¥H»´ÃP¸Ñ¨M¤F

¦pªG§A­Ì·Q­nÅýµ{¦¡§ó¥[§Ö¬½·Ç¡AMatch¨ç¼Æ­È±o§Aªá®É¶¡¬ã¨s¤@¤U¥Îªk

µ{¦¡¦p¤U

Sub Test0801()
All = Array("¤T°Ó", "»·ªF", "«H¸q")
Rn = [A1].End(4).Row
For R = 1 To Rn
  B = Application.Match(Cells(R, 2), All, 0)
  If Not IsError(B) Then '¦aÂI¬O§_¦bAll°}¦C¤º
    With Sheets(Cells(R, 2).Value)
      Ro = .Cells(Rows.Count, 1).End(3).Row '§ä¨C­¶³Ì¥½¦C
      If Ro = 1 And .[A1] = "" Then Range([A1], [A1].End(2)).Copy .[A1]     '½Æ»s¼Ð´£¦C
      Ro = Ro + 1: Range(Cells(R, 1), Cells(R, 1).End(2)).Copy .Cells(Ro, 1) '½Æ»s¸ê®Æ
    End With
  End If
Next
End Sub

Àɮצp¤U~

Test0801.rar (17.84 KB)

¤U¸ü¦¸¼Æ: 5

2020-8-1 23:46