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

[µo°Ý] ¿é¤J¸ê®Æ«á«K¦Û°Ê¦a¦¨¬°

¥»©«³Ì«á¥Ñ Hsieh ©ó 2010-5-11 20:51 ½s¿è

©w¸q¦WºÙlist
=OFFSET(Sheet1!$B$2,,,IF(COUNTA(Sheet1!$B:$B)=0,1,COUNTA(Sheet1!$B:$B)),)
ÅçÃÒ²M³æ
¤½¦¡=list
Dropdown list.rar (10.53 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2010-5-11 23:03 ½s¿è

¦^´_ 4# letugo


    ³o¼Ë¥i¯à­n¥Î¨ìVBA»²§U¤~¦æ Dropdown list.rar (14.06 KB)
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. Dim Ar()
  3. Set d = CreateObject("Scripting.Dictionary")
  4. If Target.Column = 3 Then '«ü©wÄæ¦ì
  5.    With Target.EntireColumn.Validation
  6.    .Delete
  7.    For Each a In Range(Cells(2, Target.Column), Target)
  8.      d(a.Value) = d(a.Value) + 1
  9.    Next
  10.    Do Until d.Count = 0 Or i = 20
  11.        k = Application.Large(d.items, 1)
  12.        k = Application.Match(k, d.items, 0)
  13.        ans = Application.Index(d.keys, k)
  14.        If mystr = "" Then
  15.        mystr = ans
  16.        Else
  17.        mystr = mystr & "," & ans
  18.        End If
  19.        d.Remove ans: i = i + 1
  20.    Loop
  21.    If Not IsError(mystr) And mystr <> "" Then .Add xlValidateList, Formula1:=mystr: .ShowError = False
  22.    End With
  23. End If
  24. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 9# letugo
Ãø¹D§A³£¨S¥h¤F¸Ñµ{¦¡½X¶Ü?
If Target.Column = 3 Or Target.Column = 5 Then '«ü©wÄæ¦ì

Do Until d.Count = 0 Or i = 30 'i¬°«ü©w±Æ¦W¶q

¥u¬O§â¤£­«½Æ¦W³æ­pºâ¥X²{¦¸¼Æ«á,¤@¦¸¼Æ¦h¹è¥[¤JÅçÃÒ²M³æ
´N³o»ò³æ¯ÂªºÅÞ¿è
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

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