- 帖子
- 5923
- 主題
- 13
- 精華
- 1
- 積分
- 5986
- 點名
- 0
- 作業系統
- win10
- 軟體版本
- Office 2010
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台灣基隆
- 註冊時間
- 2010-5-1
- 最後登錄
- 2022-1-23
        
|
2#
發表於 2015-6-4 17:27
| 只看該作者
回復 1# lpk187
執行表單各縣市的行政區如有相同名稱,投遞街道會有錯誤.
我住基隆一眼看出中山區沒有 一江街. 中正區沒有汀洲路3段
幫你修改一下- 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
|
|
發表於 2015-6-3 17:46
| 
