麻辣家族討論版版 - Powered by Discuz! Board

標題: [發問] 如何可使 d.keys 各項重作排序動作? [打印本頁]

作者: yangjie 時間: 2014-6-19 10:38 標題: 如何可使 d.keys 各項重作排序動作?

請教諸位大大
Dim d As Object , a
Dim r As Range
Sheets("學生資料").Activate
Set d = CreateObject("Scripting.Dictionary")
         With Sheets("學生資料")
            For Each r In .Range(.Cells(2, 1), .Cells(65536, 1).End(xlUp))
                  d(r.Value) = r.Value
            Next
         End With
   a=d.keys
   ComboBox1.List = d.keys
End Sub
請教
如何可使 d.keys 各項重作排序動作 (結果使a(0),a(1),a(2)-----是遞增排序)?
                                                         謝謝指導

作者: GBKEE 時間: 2014-6-19 11:52

回復 1# yangjie

Option Explicit
Sub Ex()
Dim d As Object, i As Integer, r As Range, A
'Sheets("學生資料").Activate
Set d = CreateObject("Scripting.Dictionary")
With Sheets("學生資料")
For Each r In .Range(.Cells(2, 1), .Cells(65536, 1).End(xlUp))
d(r.Value) = r.Value
Next
ReDim A(1 To d.Count)
For i = 1 To d.Count
A(i) = d(Application.WorksheetFunction.Small(d.KEYS, i))
Next
.ComboBox1.List = A
End With
End Sub

複製代碼

作者: Hsieh 時間: 2014-6-19 14:35

回復 1# yangjie

資料是文字或是數值?
要通用就要以字元碼排序

Sub ex()
Set d = CreateObject("Scripting.dictionary")
For Each a In Range([A1], [A1].End(xlDown))
k = 0
For i = 1 To Len(a)
k = k + Asc(Mid(a, Len(a) - i + 1, 1)) * 10 ^ i
Next
d(k) = a.Value
Next
ComboBox1.Clear
For i = 1 To d.Count
ComboBox1.AddItem d(Application.Small(d.keys, i))
Next
End Sub

複製代碼

作者: yangjie 時間: 2014-6-20 02:47

回復 2# GBKEE
謝謝GBKEE
完全了解

作者: yangjie 時間: 2014-6-20 02:56

回復 3# Hsieh
謝謝Heish 版大
For i = 1 To Len(a)
k = k + Asc(Mid(a, Len(a) - i + 1, 1)) * 10 ^ i
Next
有意思
"08" "10" "11" "01" "02"可排序成 "01"<"02"<"08" <"10"<"11" 嗎?
"01"<"1"嗎?

作者: GBKEE 時間: 2014-6-20 07:30

本帖最後由 GBKEE 於 2014-6-20 07:31 編輯

回復 5# yangjie
不要想複雜了,數字,中文,英文,可以一起來

Option Explicit
Sub Ex()
Dim d As Object, r As Range
'Sheets("學生資料").Activate
Set d = CreateObject("Scripting.Dictionary")
With Sheets("學生資料")
For Each r In .Range(.Cells(2, 1), .Cells(65536, 1).End(xlUp))
d(r.Value) = r.Value
Next
With .Cells(1, .Columns.Count).Resize(d.Count) '工作表的最右一欄
.Value = Application.WorksheetFunction.Transpose(d.KEYS)
.Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlNo, MatchCase:=True
.Parent.ComboBox1.List = Application.WorksheetFunction.Transpose(.Value)
.Clear
End With
End With
End Sub

複製代碼

作者: yangjie 時間: 2014-6-21 14:53

回復 6# GBKEE
謝謝GBKEE
是我把所需的結果沒說清楚
表單裡
TextBox1="05"    TextBox2
TextBox3="01"    TextBox4
TextBox5=""          TextBox6
TextBox7="12"    TextBox8
TextBox9="08"    TextBox10
TextBox11="02"    TextBox12
TextBox13="11"    TextBox14
如何下語法  使 TextBox2.value =3  TextBox4.value =1  TextBox6.value =""  TextBox8.value =6
                           value是它的排序

作者: GBKEE 時間: 2014-6-22 06:50

回復 7# yangjie

還是沒說清楚

請上傳檔案說明你構想的範例

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)