ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] "±Æ§Ç"/"§R°£¾ã¦C"¦³¨S¦³¨ä¥¦ªºµ{¦¡¼gªk?

[µo°Ý] "±Æ§Ç"/"§R°£¾ã¦C"¦³¨S¦³¨ä¥¦ªºµ{¦¡¼gªk?

¦U¦ì¥ý¶i ¤j®a¦n,
§Úªº¤u§@ªí¤¤±`­n¥Î¨ì±Æ§Ç/§R°£¾ã¦C ¥\¯à,¦ý¥u¦³¸ê®Æ¼W¥[,¤£¬O­n­×§ï¥¨¶°¤¤ªºµ{¦¡,¤£µM´N±o±N½d³ò©Ô¤j,ı±o³o¼Ëªº§@ªk³£¤£²z·Q.
½Ð°Ý¥H¤U¤GºØ¥\¯à,¬O§_¦³¨ä¥¦ªº¼gªk,¥i¥H¤£¥Î¤@ª½­×§ï½d³òªº?
a) ±Æ§Ç
Sub sorting()
    Windows("Pre-paid_HK Cost.xlsx").Activate
    Range("A7").Select
    ActiveWorkbook.Worksheets("NONE NE").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("NONE NE").AutoFilter.Sort.SortFields.Add Key:= _
        Range("A2:A1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("NONE NE").AutoFilter.Sort.SortFields.Add Key:= _
        Range("E2:E1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("NONE NE").AutoFilter.Sort.SortFields.Add Key:= _
        Range("D2:D1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("NONE NE").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
b) §R°£¾ã¦C¥i¨£Àx¦s®æ
   With Workbooks("Pre-paid_Format.xlsx").Sheets("NE")
     .Range("A:AB").AutoFilter Field:=24, Criteria1:="Taipei"       '¿z¿ï±ø¥ó
    Rows("2:1000").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Delete Shift:=xlUp

¦^´_ 20# Hsieh

ª©¤j,
¤F¸Ñ,ÁÂÁ§Aªº¸Ñ»¡.

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2012-4-30 19:33 ½s¿è

¦^´_ 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
play.gif
©Ò¥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¥À
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 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

TOP

¦^´_ 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
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

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

TOP

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

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

TOP

¦^´_ 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
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 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

TOP

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

TOP

        ÀR«ä¦Û¦b : ¨Ã«D¦³¿ú¾{¬O§Ö¼Ö¡A°Ý¤ßµL·\¤ß³Ì¦w¡C
ªð¦^¦Cªí ¤W¤@¥DÃD