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

Dim Sh As Worksheet, d As Object, d2 As Object, d3 As Object
Private Sub UserForm_Initialize()
Dim i As Single, Rng As Range
Set Sh = Sheets("3+2郵遞區號檔")
Set d = CreateObject("scripting.dictionary")
Set Rng = Sh.Cells(2, "b")
Do While Rng <> ""
If d.exists(Rng.Value) = False Then
Set d(Rng.Value) = Rng
Else
Set d(Rng.Value) = Union(Rng, d(Rng.Value))
End If
Set Rng = Rng.Offset(1)
Loop
ComboBox1.List = d.KEYS
TextBox1 = ""
End Sub
Private Sub ComboBox1_Change()
Dim R As Range
TextBox1 = ""
Set d2 = CreateObject("scripting.dictionary")
For Each R In d(ComboBox1.Value).Offset(, 1).Cells
If d2.exists(R.Value) = False Then
Set d2(R.Value) = R
Else
Set d2(R.Value) = Union(R, d2(R.Value))
End If
Next
ComboBox2.Clear
ComboBox2.List = d2.KEYS
End Sub
Private Sub ComboBox2_Change()
Dim R As Range
TextBox1 = ""
ComboBox3.Clear
Set d3 = CreateObject("scripting.dictionary")
If ComboBox2.ListIndex > -1 Then
For Each R In d2(ComboBox2.Value).Offset(, 1).Cells
If d3.exists(R.Value) = False Then
Set d3(R.Value) = R
Else
Set d3(R.Value) = Union(R, d3(R.Value))
End If
Next
ComboBox3.List = d3.KEYS
End If
End Sub
Private Sub ComboBox3_Change()
TextBox1 = ""
If ComboBox3.ListIndex > -1 Then TextBox1.Text = Sh.Cells(d3(ComboBox3.Value).Row, "A")
'ListIndex = -1 值不在List中
End Sub
Private Sub CommandButton1_Click()
Dim Rng As Range
If ComboBox3.ListIndex = -1 Then Exit Sub
With Sheets("工作表1")
Set Rng = .Range("A" & .Cells(.Rows.Count, 1).End(xlUp).Row + 1)
End With
With Rng
.Cells = TextBox1.Text
.Cells(1, "b") = ComboBox1.Value
.Cells(1, "c") = ComboBox2.Value
.Cells(1, "d") = ComboBox3.Value
.Cells(1, "e") = d3(ComboBox3.Value).Cells(1, "b")
End With
End Sub

複製代碼