Hsieh

¦^´_ 10# PJChen
2010ª©¥»¬O¥i¥H¦hÁä±Æ§Ç¡A§A©Ò¿ýªºµ{¦¡½X§Y¥i
­Y­n¥H2003±Æ§Ç­­¨î¡A¥i§Q¥Î±Æ§ÇÄæ¦ì¶¶§Ç¡A¨Ï¥Î°j°é¥h±Æ¦C§Y¥i¡A¦ý³o­n§A»¡©úÄæ¦ì¶¶§Ç

PJChen

¦^´_ 11# Hsieh
ª©¤j±z¦n,§Ú¥Îªº¬OExcel 2010 format:
­è¤~©Ò¿ý»sªº±Æ§Ç¬°:

1.     Windows("BCM Order_Format.xlsx").Activate
   
2.     Sheets("PO").Select
   
3.     Range("D2").Select
   
4.     ActiveWorkbook.Worksheets("PO").AutoFilter.Sort.SortFields.Clear
   
5.     ActiveWorkbook.Worksheets("PO").AutoFilter.Sort.SortFields.Add Key:=Range( _
   
6.         "D2:D600"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
   
7.         xlSortNormal
   
8.     ActiveWorkbook.Worksheets("PO").AutoFilter.Sort.SortFields.Add Key:=Range( _
   
9.         "Z2:Z600"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
   
10.         xlSortNormal
    
11.     ActiveWorkbook.Worksheets("PO").AutoFilter.Sort.SortFields.Add Key:=Range( _
    
12.         "H2:H600"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    
13.         xlSortNormal
    
14.     ActiveWorkbook.Worksheets("PO").AutoFilter.Sort.SortFields.Add Key:=Range( _
    
15.         "G2:G600"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    
16.         xlSortNormal
    
17.     ActiveWorkbook.Worksheets("PO").AutoFilter.Sort.SortFields.Add Key:=Range( _
    
18.         "E2:E600"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    
19.         xlSortNormal
    
20.     With ActiveWorkbook.Worksheets("PO").AutoFilter.Sort
    
21.         .Header = xlYes
    
22.         .MatchCase = False
    
23.         .Orientation = xlTopToBottom
    
24.         .SortMethod = xlPinYin
    
25.         .Apply

½Æ»s¥N½X

G¤j¼gªº¾Ú»¡¥u¯à¦Ü3²Õ,©Ò¥H§Ú­×§ï¬°¥H¤U5²Õªº¤èªk´N¦æ¤£³q?

1. With Workbooks("BCM Order_Format.xlsx")
   
2. With .Sheets("PO")
   
3.          .Range("A1").CurrentRegion.Sort Key1:=.Range("D2"), Order1:=xlAscending,
   
4.           Key2:=.Range("Z2"), Order2:=xlAscending, _
   
5.           Key3:=.Range("H2"), Order3:=xlAscending, _
   
6.           Key4:=.Range("G2"), Order4:=xlAscending, _
   
7.           Key5:=.Range("E2"), Order5:=xlAscending, _
   
8.         Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:= xlTopToBottom, SortMethod:=xlStroke, _
   
9.         DataOption1:=xlSortNormal, _
   
10.         DataOption2:=xlSortNormal, _
    
11.         DataOption3:=xlSortNormal, _
    
12.         DataOption4:=xlSortNormal, _
    
13.         DataOption5:=xlSortNormal
    
14.     End With
    
15.     End With

½Æ»s¥N½X

½Ð°Ý¨Ì§Ú·Q­nªº,ÁÙ¦³¤°»ò¤èªk¥i¥H¥Î?

PJChen

¦^´_ 11# Hsieh
¸É¥R:
§Ú¥Î¿ý»sªº·Pı¤£¹ê¥Î,©Ò­n¤~·Q­n¥Î"Äæ¦ì½d³ò"§@¬°±Æ§Ç,¦Ó¤£­n«ü©wÄæ+¦C,§Æ±æ¯à¦³§ó¦nªº«Øij!

register313

¦^´_ 13# PJChen

2010ª©
  ¥Î8¼Óªºµ{¦¡§Y¥i
  µ{¦¡²Ä1¦æ³Ì«á¤Ö¤F ªÅ¥Õ »P _
   .Range("A1").CurrentRegion.Sort Key1:=.Range("D2"), Order1:=xlAscending, _
   ¦A¸Õ¸Õ¬Ý
©Î

1. Sub xx()
   
2. Ar = Array(5, 4, 3, 2, 1)            '±qÀu¥ý¶¶§Ç³Ì§CªºÄæ¦ì¶}©l,­n±Æ´XÄæ¬Ò¥i
   
3. For i = 0 To UBound(Ar)
   
4.   [A1].CurrentRegion.Sort Key1:=Cells(2, Ar(i)), Order1:=1, Header:=xlGuess  '1:»¼¼W 2:»¼´î
   
5. Next
   
6. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 12# PJChen
2010»P2003±Æ§Ç¤èªk¤£¦P
§A©Ò¤W¶ÇªºÀÉ®×Äæ¦ì»Pµ{¦¡½X¤£²Å
¨Ì·Ó¤j·N­×§ï¦p¤U

1. Sub nn()
   
2. With Workbooks("BCM Order_Format.xlsx")
   
3.    With .Sheets("PO")
   
4.    Set Rng = Union(.[D2], .[Z2], .[H2], .[G2], .[E2])
   
5.     .AutoFilter.Sort.SortFields.Clear
   
6.     For Each A In Rng
   
7.        .AutoFilter.Sort.SortFields.Add Key:=.Range(A, A.End(xlDown)) _
   
8.         , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
   
9.         xlSortNormal
   
10.     Next
    
11.     With .AutoFilter.Sort
    
12.         .Header = xlYes
    
13.         .MatchCase = False
    
14.         .Orientation = xlTopToBottom
    
15.         .SortMethod = xlPinYin
    
16.         .Apply
    
17.     End With
    
18.   End With
    
19. End With
    
20. End Sub

½Æ»s¥N½X

PJChen

¦^´_ 15# Hsieh
ª©¤j,
§Ú¦Û¤v¸Õ¤F¤@¤U,±Æ§Ç¨S¦³¦¨¥\,¤]·Q½Ð°Ý¬°¦óµ{¦¡¤¤¤£¥Î±a¥XÄæ¦ìªº°_¨´?
ªþ¤WÀÉ®× BCM Order_Format.zip (27.33 KB)

¤U¸ü¦¸¼Æ: 7

2012-4-29 23:23

¦^´_ 14# register313
Sorry!§A¼gªº¤Óºë²,§Ú¤£ª¾¹D«ç»ò¸ò8Fªºµ{¦¡¦X¦b¤@°_,½ÐÀ°¦£¤@¤U.

PJChen

¦^´_ 15# Hsieh
¸É¥R:
Äæ¦ìA:AT   sort¶¶§ÇD.Z.H.G.E

Hsieh

¦^´_ 17# PJChen

1. Sub nn()
   
2. With Workbooks("BCM Order_Format.xlsx")
   
3.    With .Sheets("PO")
   
4.    Set b = .Range("A1").CurrentRegion
   
5.    a = Array(4, 26, 8, 7, 5)
   
6.     .AutoFilter.Sort.SortFields.Clear
   
7.     For i = 0 To 4
   
8.        .AutoFilter.Sort.SortFields.Add Key:=b.Columns(a(i)) _
   
9.         , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
   
10.         xlSortNormal
    
11.     Next
    
12.     With .AutoFilter.Sort
    
13.         .Header = xlYes
    
14.         .MatchCase = False
    
15.         .Orientation = xlTopToBottom
    
16.         .SortMethod = xlPinYin
    
17.         .Apply
    
18.     End With
    
19.   End With
    
20. End With
    
21. End Sub

½Æ»s¥N½X

PJChen

¦^´_ 18# Hsieh
Á¤j,
¦¨¥\¤F,³oºØµ{¦¡ªº¼gªk³£¤£¥Î«ü©úÄæ¦ì½d³òªº¬O¶Ü? a = Array(4, 26, 8, 7, 5)³¡¥÷,¥i¥H§ï¥ÎÄæ¦ì¶Ü?­n¦p¦ó¼g,§Ú§ï¬° a = Array(D,Z,H,G,E),´N¦æ¤£³q¤F,
¥t¥~ Set b = .Range("A1").CurrentRegion¬O«ü±qA1ªºÄæ¦ì¶}©l±Æ§Ç¬O§a?­Y±Æ§Çªº½d³ò¬OA5:Z200,¬O§_À³§ï¬° Set b = .Range("A5").CurrentRegion

Hsieh

¦^´_ 19# PJChen
Set b = .Range("A1").CurrentRegion
¬O§âÅܼÆb³]¦¨»PA1Àx¦s®æ©Ò¦³¬Û³sªº½d³ò
´N¬O»¡±qA1¦V¥k¨ì¾ãÄæªÅ¥Õ¡B¦V¤U¨ì¾ã¦CªÅ¥Õ©Ò§Î¦¨ªº½d³ò¡A½Ð¬Ý°Êµe¡C

¤U¸ü (384.92 KB)

2012-4-30 08:39

©Ò¥H¡A¦b2010ª©¥»¤¤¡A¿z¿ï±Æ§ÇÄæ¦ì¬O¥ý·s¼W©Ò¦³±Æ§Ç¼h¯Å«á
¦A¤@¦¸¨Ì·Ó³]©w¼h¯Å¥ý«á¥h±Æ¦C(Apply)

a = Array(4, 26, 8, 7, 5)
§ï¦¨a = Array(D,Z,H,G,E),´N¦æ¤£³q¤F
³o¬O¦]¬°D,Z,H,G,E³o·|µø¬°5­ÓÅܼÆD,Z,H,G,E¡A¦Ó¤£¬O³o5­Ó¦r¥À
a = Array("D","Z","H","G","E")
³o¼Ë¤~·|§â°}¦C¤¸¯ÀŪ¬°¦r¥À