teratail header banner
teratail header banner
質問するログイン新規登録

質問編集履歴

2

郵便番号データを利用する前提でソースの改定、簡素化

2018/01/05 16:19

投稿

zorac
zorac

スコア42

title CHANGED
File without changes
body CHANGED
@@ -46,60 +46,52 @@
46
46
 
47
47
  ###該当のソースコード
48
48
  ```VBA
49
+ '郵便番号データダウンロードサービスで入手可能な郵便番号データを利用する
50
+ Private Sub UserForm_Initialize()
49
- Option Explicit
51
+ With ListView1
50
-
52
+ .View = lvwReport
53
+ .HideSelection = False
54
+ .FullRowSelect = True
55
+ .ColumnHeaders.Add , "_X0401-X0402", "全国地方公共団体コード", 120
56
+ .ColumnHeaders.Add , "_OldPostalCode", "旧郵便番号", 120
57
+ .ColumnHeaders.Add , "_PostalCode", "郵便番号", 80
58
+ .ColumnHeaders.Add , "_Prefectures", "都道府県名", 80
59
+ End With
60
+ End Sub
61
+
62
+
51
63
  Private Sub TextBox1_Change()
52
- Dim lastRow As Long
53
- Dim dataSet As Variant
64
+ '-------------------- ListViewのクリア ココカラ --------------------
54
- Dim searchResult() As String
65
+ ListView1.ListItems.Clear
55
- Dim count As Long
66
+ '-------------------- ListViewのクリア ココマデ --------------------
56
- Dim t As Single
57
67
 
68
+
58
69
  '-------------------- データの検索 ココカラ --------------------
70
+ Dim t As Single
71
+ Dim ws As Worksheet
59
72
  t = Timer()
60
- With Worksheets("Sheet1")
73
+ Set ws = Worksheets("Sheet1")
74
+
75
+ With ws
76
+ Dim lastRow As Long
77
+ Dim dataSet As Variant
61
- lastRow = .Cells(1048576, 1).End(xlUp).Row
78
+ lastRow = .Cells(.Rows.count, 1).End(xlUp).Row
62
- dataSet = .Range(.Cells(1, 1), .Cells(lastRow, 7))
79
+ dataSet = .Range(.Cells(1, 1), .Cells(lastRow, 4))
63
- ReDim searchResult(lastRow - 1, 5)
64
80
 
65
81
  Dim i As Long
66
82
  For i = 2 To lastRow
67
- If dataSet(i, 7) Like "*" & TextBox1 & "*" Then
83
+ If dataSet(i, 3) Like "*" & TextBox1 & "*" Then
84
+ With ListView1.ListItems.Add
85
+ .Text = dataSet(i, 1) '全国地方公共団体コード
68
- searchResult(count, 0) = dataSet(i, 1)
86
+ .SubItems(1) = dataSet(i, 2) '旧郵便番号
69
- searchResult(count, 1) = dataSet(i, 3)
87
+ .SubItems(2) = dataSet(i, 3) '郵便番号
70
- searchResult(count, 2) = dataSet(i, 4)
88
+ .SubItems(3) = dataSet(i, 4) '都道府県名
71
- searchResult(count, 3) = dataSet(i, 5)
72
- searchResult(count, 4) = dataSet(i, 6)
73
- searchResult(count, 5) = dataSet(i, 7)
74
- count = count + 1
89
+ End With
75
90
  End If
76
91
  Next i
77
92
  End With
78
93
  Debug.Print "データの検索: " & Round(Timer() - t, 2)
79
94
  '-------------------- データの検索 ココマデ --------------------
80
-
81
-
82
- '-------------------- ListViewのクリア ココカラ --------------------
83
- ListView1.ListItems.Clear
84
- If count = 0 Then Exit Sub '検索結果が0ならば処理終了
85
- '-------------------- ListViewのクリア ココマデ --------------------
86
-
87
-
88
-
89
- '-------------------- ListViewへの検索結果反映 ココカラ --------------------
90
- t = Timer()
91
- For i = 0 To count - 1
92
- With ListView1.ListItems.Add
93
- .Text = searchResult(i, 0)
94
- .SubItems(1) = searchResult(i, 1)
95
- .SubItems(2) = searchResult(i, 2)
96
- .SubItems(3) = searchResult(i, 3)
97
- .SubItems(4) = searchResult(i, 4)
98
- .SubItems(5) = searchResult(i, 5)
99
- End With
100
- Next i
101
- Debug.Print "ListViewへの反映: " & Round(Timer() - t, 2) & vbCrLf
102
- '-------------------- ListViewへの検索結果反映 ココマデ --------------------
103
95
  End Sub
104
96
  ```
105
97
 

1

コメントで指摘いただいた箇所を修正しました。

2018/01/05 16:19

投稿

zorac
zorac

スコア42

title CHANGED
File without changes
body CHANGED
@@ -64,7 +64,7 @@
64
64
 
65
65
  Dim i As Long
66
66
  For i = 2 To lastRow
67
- If dataSet(i, 9) Like "*" & TextBox1 & "*" Then
67
+ If dataSet(i, 7) Like "*" & TextBox1 & "*" Then
68
68
  searchResult(count, 0) = dataSet(i, 1)
69
69
  searchResult(count, 1) = dataSet(i, 3)
70
70
  searchResult(count, 2) = dataSet(i, 4)