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

[µo°Ý] ³q¹L¸ê®ÆÅçÃÒ¤£­«½Æ­È©M±Æ§Ç?

[µo°Ý] ³q¹L¸ê®ÆÅçÃÒ¤£­«½Æ­È©M±Æ§Ç?

AÄæ¤W¦³¤@²ÕMember¦WºÙ¡A¦p¦ó¦bG2¤W¥Î¸ê®ÆÅçÃÒ¤£­«½Æ­È©M±Æ§Ç¡H

Member
B
A
B
B
C
D
E
A
B
E
D
G
A
B
B
F


½Ð¦U¦ì½ç±Ð¡AÁÂÁ¡I

¦^´_ 1# maiko

¸Õ¸Õ¬Ý
  1. Sub test()
  2. '
  3.    
  4.     Columns("A:A").Copy
  5.     Columns("G:G").PasteSpecial Paste:=xlPasteValues '¶K¤W­È
  6.     Application.CutCopyMode = False
  7.     '²¾°£­«½Æ
  8.     ActiveSheet.Range("G1:G" & [g65536].End(xlUp).Row).RemoveDuplicates Columns:=1, Header:=xlYes
  9.     '±Æ§Ç
  10.     ActiveWorkbook.Worksheets("¤u§@ªí1").Sort.SortFields.Add Key:=Range("G1:G" & [g65536].End(xlUp).Row), _
  11.         SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  12.     With ActiveWorkbook.Worksheets("¤u§@ªí1").Sort
  13.         .SetRange Range("G1:G" & [g65536].End(xlUp).Row)
  14.         .Header = xlYes
  15.         .MatchCase = False
  16.         .Orientation = xlTopToBottom
  17.         .SortMethod = xlPinYin
  18.         .Apply
  19.     End With
  20. End Sub
½Æ»s¥N½X
PKKO

TOP

¦^´_  maiko

¸Õ¸Õ¬Ý
PKKO µoªí©ó 2015-4-21 19:24



   
µ{¦¡¨ì .Apply ªº®É­Ô¥X²{¿ù»~¡A¦p¹Ï¡G
Error.png
2015-4-21 19:29


¦Ó¥B¡A¤£¬O¸ê®ÆÅçÃÒ¡C

TOP

¦^´_ 1# maiko
§O»¡½ç±Ð¤F¡A³s°ÝÃD³£¨S¬ÝÀ´¡C¡C¡C
§A¤£§â°ÝÃD¦A»¡²M·¡ÂI¶Ü?
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

¦^´_ 3# maiko
§Ú­ì¥»´£¨Ñªºµ{¦¡½X¬°,½Æ»sAÄæ¨ìGÄæ=>²¾°£­«½Æ(«O¯dªíÀY)=>±Æ§Ç(­^¤å¤è¦¡)
¤£À´§A©Ò¿×ªº¸ê®ÆÅçÃÒ,­Y§A«üªº¬O¤U©Ô¿ï³æ
¸Õ¸Õ¬Ý,ÀÉ®×¥i¥H¥¿±`¹B§@
test.rar (16.45 KB)
  1. Sub test()
  2. '
  3.    
  4.     Columns("A:A").Copy
  5.     Columns("G:G").PasteSpecial Paste:=xlPasteValues '¶K¤W­È
  6.     Application.CutCopyMode = False
  7.     '²¾°£­«½Æ
  8.     ActiveSheet.Range("G1:G" & [g65536].End(xlUp).Row).RemoveDuplicates Columns:=1, Header:=xlYes
  9.     '±Æ§Ç
  10.     ActiveWorkbook.Worksheets("¤u§@ªí1").Sort.SortFields.Add Key:=Range("G1:G" & [g65536].End(xlUp).Row), _
  11.         SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  12.     With ActiveWorkbook.Worksheets("¤u§@ªí1").Sort
  13.         .SetRange Range("G1:G" & [g65536].End(xlUp).Row)
  14.         .Header = xlYes
  15.         .MatchCase = False
  16.         .Orientation = xlTopToBottom
  17.         .SortMethod = xlPinYin
  18.         .Apply
  19.     End With
  20.     For i = 2 To [g65536].End(xlUp).Row
  21.         name1 = name1 & Cells(i, 7) & ","
  22.     Next
  23.     name2 = Mid(name1, 1, Len(name1) - 1)
  24.     With Range("G2:G" & [a65536].End(xlUp).Row).Validation
  25.         .Delete
  26.         .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
  27.         xlBetween, Formula1:=name2
  28.         .IgnoreBlank = True
  29.         .InCellDropdown = True
  30.         .InputTitle = ""
  31.         .ErrorTitle = ""
  32.         .InputMessage = ""
  33.         .ErrorMessage = ""
  34.         .IMEMode = xlIMEModeNoControl
  35.         .ShowInput = True
  36.         .ShowError = True
  37.     End With
  38.     Range("G2:G" & [g65536].End(xlUp).Row).ClearContents
  39. End Sub
½Æ»s¥N½X
PKKO

TOP

¦^´_ 5# PKKO


    µ{¦¡Á`¬O¨ì .Apply ³o¤@¨B´N¥X²{ 1004 ¿ù»~¡G
Error.png
2015-4-22 01:43

TOP

¦^´_ 4# stillfish00


    ´N¬O§âAÄæ¤WªºMember¦WºÙ¡G
B
A
B
B
C
D
E
A
B
E
D
G
A
B
B
F
°µ¤@­Ó¤U©Ô¿ï³æ¡A¦Ó¥B³o¿ï³æ¬O­n¤£­«½Æ¡B¥Ñ¤p¨ì¤j±Æ§Çªº¡C

¥i¯à¥Î¸ê®ÆÅçÃÒ¤j®a·|¤£À´¡A¨ä¹ê´N¬O¤U©Ô¿ï³æ¡C

TOP

¦^´_ 7# maiko

PKKO¤j¤W¶ÇªºÀɮשMµ{¦¡½X¡A¥i¥H¹F¦¨§Aªº°ÝÃD¡A¤]¥i¥H¥¿±`°õ¦æ¡A¬Æ¦Ü¨S¦³¥X²{§APO¥X¨Óªº¿ù»~¡A¦ó¤£§â§AªºÀɮפW¶Ç¤W¨ÓÅý¤j®aÀ°§AÀ˵ø©O¡H

TOP

        ÀR«ä¦Û¦b : ª¾ÃÑ­n¥Î¤ßÅé·|¡A¤~¯àÅܦ¨¦Û¤vªº´¼¼z¡C
ªð¦^¦Cªí ¤W¤@¥DÃD