Hsieh

©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)

¤U¸ü¦¸¼Æ: 26

2010-5-11 20:51

Hsieh

¦^´_ 4# letugo


    ³o¼Ë¥i¯à­n¥Î¨ìVBA»²§U¤~¦æ Dropdown list.rar (14.06 KB)

¤U¸ü¦¸¼Æ: 25

2010-5-11 23:03

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

Hsieh

¦^´_ 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»ò³æ¯ÂªºÅÞ¿è