[µo°Ý] ¤ñ¹ï¸ê®Æ½Æ»s¦Ü¤u§@ªí¨Ã±Æ§Ç

b9208

¦U¦ì«e½ú
1. ¨Ì·Ó ¡ª³æ¦ì¡ª¤u§@ªí¤§D3:G3¡u³æ¦ì½s½X¡v¦W³æ¡A½Æ»s¡ª¸ê®Æ¡ª¤u§@ªí¤º¬Û¦P³æ¦ì½s½Xªº¸ê®Æ¦Ü¡ª³æ¦ì¡ª¤u§@ªí¡C
2. ¦A¨Ì·Ó³æ¦ì½s½X¡B¤é´Á¤Î©m¦W¶¶§Ç±Æ§Ç¸ê®Æ¡C
·PÁ«ü¾É

b211.rar (4.48 KB)

¤U¸ü¦¸¼Æ: 19

2013-3-7 22:15

Hsieh

¦^´_ 1# b9208

1. Sub ex()
   
2. Dim A As Range, Rng As Range
   
3. With Sheets("¸ê®Æ")
   
4.    For Each A In .Range(.[F6], .[F6].End(xlDown))
   
5.       If Application.CountIf(Sheets("³æ¦ì").Rows(3), A) > 0 Then
   
6.          If Rng Is Nothing Then Set Rng = A Else Set Rng = Union(Rng, A)
   
7.       End If
   
8.    Next
   
9. End With
   
10. With Sheets("³æ¦ì")
    
11. .[A19].CurrentRegion.Offset(1) = ""
    
12. If Not Rng Is Nothing Then Rng.EntireRow.Copy .[A20]
    
13. .[A19].CurrentRegion.Sort key1:=.[F20], key2:=.[E20], key3:=.[H20], Header:=xlYes
    
14. End With
    
15. End Sub

½Æ»s¥N½X

b9208

¦^´_ 2# Hsieh
·PÁÂHsiehª©¥D
¥Ø«e¨Ï¥Î¤Wok
§V¤O¾Ç²ßµ{¦¡Å޿褤
ÁÂÁÂ

GBKEE

¦^´_ 3# b9208

1. Option Explicit
   
2. Sub Ex()  'AdvancedFilter ¤èªk (¶i¶¥¿z¿ï)
   
3.     Dim Rng  As Range, CopyTo As Range
   
4.     Set Rng = Sheets("¸ê®Æ").Range("a5").CurrentRegion      '¶i¶¥¿z¿ïªº: ¸ê®Æ²M³æ½d³ò(¸ê®Æ®w)
   
5.     'CurrentRegion ÄÝ©Ê ¶Ç¦^ Range ª«¥ó¡A¸Óª«¥ó¥Nªí¥Ø«eªº°Ï°ì¡C¥Ø«e°Ï°ì¬O«ü¥H¥ô·NªÅ¥Õ¦C¤ÎªÅ¥ÕÄ檺²Õ¦X¬°Ãä¬Éªº½d³ò¡C°ßŪ¡C
   
6.     With Sheets("³æ¦ì")
   
7.         Set CopyTo = .Range(.[A19], .[A19].End(xlToRight))   '«ü©w³Q½Æ»s¦Cªº¥Ø¼Ð½d³ò
   
8.         Rng.AdvancedFilter xlFilterCopy, .Range(.[C3], .[C3].End(xlDown)), CopyTo, True
   
9.                                         '.Range(.[C3], .[C3].End(xlDown))      '¶i¶¥¿z¿ï:·Ç«h½d³ò
   
10.     End With
    
11.     With CopyTo.CurrentRegion
    
12.         .Sort key1:=.Cells(6), key2:=.Cells(3), key3:=.Cells(8), Header:=xlYes
    
13.     End With
    
14.         'key1:=.Cells(6) '²Ä¤@­Ó±Æ§ÇÄæ¦ì: .Cells(6) ->³æ¦ì½s½X [F19]
    
15.         'key2:=.Cells(3) '²Ä¤G­Ó±Æ§ÇÄæ¦ì: .Cells(3) ->¤é´Á [C19]
    
16.         'key3:=.Cells(8) '²Ä¤T­Ó±Æ§ÇÄæ¦ì: .Cells(8) ->©m¦W [H19]
    
17. End Sub

½Æ»s¥N½X

¤U¸ü (39.2 KB)

2013-3-8 09:18

b9208

¦^´_ 4# GBKEE
·PÁÂGBKEE
«ü¾É¿z¿ï±ø¥óÀx¦s®æ³]­p¤è¦¡
ÁÂÁÂ

b9208

¦^´_ 4# GBKEE
¦A½Ð±Ðª©¥D
±Æ§Ç«á¡u³æ¦ì½s½X¡B¤é´Á¤Î©m¦W¡v¤T­ÓÄæ¦ì¸ê®Æ¬Û¦PªÌ¡A
±q²Ä¤Gµ§¬Û¦P¸ê®Æ¶}©l¡A¥þ¦C¡]A~MÄæ¡^¸ê®ÆÀx¦s®æ¶ñº¡¶À¦â¡C¦p¤U¹Ï¤ù¡C
ÁÂÁ«ü¾É

¤U¸ü (52.97 KB)

2013-3-9 08:07

GBKEE

¦^´_ 6# b9208
¬O³o¼Ë¶Ü?

1. Option Explicit
   
2. Sub Ex()  'AdvancedFilter ¤èªk (¶i¶¥¿z¿ï)
   
3.     Dim Rng  As Range, CopyTo As Range, i As Integer
   
4.     Dim A As String, B As String
   
5.     Set Rng = Sheets("¸ê®Æ").Range("a5").CurrentRegion      '¶i¶¥¿z¿ïªº: ¸ê®Æ²M³æ½d³ò(¸ê®Æ®w)
   
6.     'CurrentRegion ÄÝ©Ê ¶Ç¦^ Range ª«¥ó¡A¸Óª«¥ó¥Nªí¥Ø«eªº°Ï°ì¡C¥Ø«e°Ï°ì¬O«ü¥H¥ô·NªÅ¥Õ¦C¤ÎªÅ¥ÕÄ檺²Õ¦X¬°Ãä¬Éªº½d³ò¡C°ßŪ¡C
   
7.     With Sheets("³æ¦ì")
   
8.         Set CopyTo = .Range(.[A19], .[A19].End(xlToRight))   '«ü©w³Q½Æ»s¦Cªº¥Ø¼Ð½d³ò
   
9.         CopyTo.CurrentRegion.Interior.ColorIndex = 0         'Àx¦s®æ©³¦â: ³]¬°µL
   
10.         Rng.AdvancedFilter xlFilterCopy, .Range(.[c3], .[c3].End(xlDown)), CopyTo, True
    
11.                                         '.Range(.[C3], .[C3].End(xlDown))      '¶i¶¥¿z¿ï:·Ç«h½d³ò
    
12.     End With
    
13.     With CopyTo.CurrentRegion
    
14.         .Sort key1:=.Cells(6), key2:=.Cells(3), key3:=.Cells(8), Header:=xlYes
    
15.         For i = 2 To .Rows.Count - 1
    
16.             A = .Rows(i).Cells(3) & .Rows(i).Cells(6) & .Rows(i).Cells(8)
    
17.             B = .Rows(i + 1).Cells(3) & .Rows(i + 1).Cells(6) & .Rows(i + 1).Cells(8)
    
18.             If A = B Then
    
19.                 .Rows(i).Interior.Color = vbYellow             'Àx¦s®æ©³¦â: ³]¬°¶À¦â
    
20.                 .Rows(i + 1).Interior.Color = vbYellow
    
21.             End If
    
22.         Next
    
23.         
    
24.     End With
    
25.         'key1:=.Cells(6) '²Ä¤@­Ó±Æ§ÇÄæ¦ì: .Cells(6) ->³æ¦ì½s½X [F19]
    
26.         'key2:=.Cells(3) '²Ä¤G­Ó±Æ§ÇÄæ¦ì: .Cells(3) ->¤é´Á [C19]
    
27.         'key3:=.Cells(8) '²Ä¤T­Ó±Æ§ÇÄæ¦ì: .Cells(8) ->©m¦W [H19]
    
28. End Sub

½Æ»s¥N½X

b9208

¦^´_ 7# GBKEE
«D±`·PÁ«ü¾É
¦ý°õ¦æ«á¡A¨S¦³±N¡ª¸ê®Æ¡ª¤u§@ªí²Å¦X±ø¥óªº¸ê®Æ¦C½Æ»s¨ì¡ª³æ¦ì¡ª¤u§@ªí¡C
ÁÂÁÂ

GBKEE

¦^´_ 8# b9208
4#¹Ï¤ù   A3 -> ¶i¶¥¿z¿ï·Ç«hÄæ¦ì ³æ¦ì½s½X  »P¤u§@ªí[¸ê®Æ] ªº³æ¦ì½s½XÄæ¦ì¦WºÙ­n¤@¼Ë

b9208

¦^´_ 9# GBKEE
GBKEEª©¥D
°õ¦æ«á²Ä¤Gµ§¬Û¦P¸ê®Æ¦C¨S¦³½Æ»s¨ì¡ª³æ¦ì¡ª¤u§@ªí¡C¦pªþ¥ó

b3.rar (10.63 KB)

¤U¸ü¦¸¼Æ: 9

2013-3-9 19:09