Sub TEST1()
'シートをクリアする
Sheets("検索").Range("H2:H1000").Clear
'部分一致で抽出する
j = 1
For i = 2 To Sheets("DB").Cells(Rows.Count, "A").End(xlUp).Row
If InStr(Sheets("DB").Cells(i, "B"), Sheets("検索").Range("G1")) > 0 Then
j = j + 1
'値を抽出する
Sheets("検索").Cells(j, "H") = Sheets("DB").Cells(i, "B")
End If
Next
End Sub
「佐藤」で絞り込みをしてみます。
「佐藤」で絞り込み
VBAコードを実行すると、社員名簿から、「佐藤」を含む名前を抽出することができます。
社員名簿から、「佐藤」を含む名前を抽出することができました。
Changeイベントを作成
次は、Changeイベントを作成して、絞りこむ値を入力したタイミングで、名前を抽出してみます。
「検索」シートのシートモジュールにVBAコードを記載します。
Private Sub Worksheet_Change(ByVal Target As Range)
'「G1」のセルが変更されたら実行する
If Target.Address(False, False) = "G1" Then
Call TEST1 '検索値を絞りこむ
End If
End Sub
絞り込む値を入力してみます。
絞りこむ値を入力
絞り込む値を入力したタイミングで、名前を抽出できます。
値を入力したタイミングで、名前を抽出できる
絞り込む値を入力したタイミングで、名前を抽出できました。
入力規則のリストを設定
抽出した値からプルダウンリストを使って選択できるように、入力規則のリストを設定します。
入力規則を選択します。
入力規則を設定
プルダウンリストに、抽出した値を設定していきます。
抽出した値をプルダウンリストに設定
入力値の種類を「リスト」として、元の値に数式を入力して、抽出した値を取得します。
入力する数式は、「=OFFSET($H$2,,,COUNTA($H:$H)-1)」という感じです。
検索値にプルダウンリストを設定することができます。
プルダウンリストから検索値を入力することができます。
プルダウンリストから選択できる
プルダウンリストから検索値を入力することができました。
SendKeysを使う
絞りこみする値を入力したタイミングで、プルダウンリストを表示できるようにします。
使うVBAコードは、SendKeysになります。
Sub TEST1()
'シートをクリアする
Sheets("検索").Range("H2:H1000").Clear
'部分一致で抽出する
j = 1
For i = 2 To Sheets("DB").Cells(Rows.Count, "A").End(xlUp).Row
If InStr(Sheets("DB").Cells(i, "B"), Sheets("検索").Range("G1")) > 0 Then
j = j + 1
'値を抽出する
Sheets("検索").Cells(j, "H") = Sheets("DB").Cells(i, "B")
End If
Next
'検索するセルを選択する
Sheets("検索").Range("G2").Select
SendKeys "%{DOWN}" 'プルダウンリストを表示する
End Sub
絞り込む値を入力してみます。
絞りこむ値を入力してみる
絞り込む値を入力したタイミングで、プルダウンリストを表示できます。
自動でプルダウンリストを表示できる
絞り込む値を入力したタイミングで、プルダウンリストを表示できました。
これで、検索値を入力しやすくなります。
検索するVBAコードを作成
プルダウンリストで選択した値を使って検索するVBAコードは、次のようになります。
Sub TEST2()
'値を検索する
For i = 2 To Sheets("DB").Cells(Rows.Count, "A").End(xlUp).Row
If Sheets("DB").Cells(i, "B") = Sheets("検索").Range("G2") Then
Sheets("検索").Range("B3") = Sheets("DB").Cells(i, "A") 'ID
Sheets("検索").Range("D3") = Sheets("DB").Cells(i, "C") '性別
Sheets("検索").Range("B4") = Sheets("DB").Cells(i, "B") '氏名
Sheets("検索").Range("B5") = Sheets("DB").Cells(i, "D") '部署
Sheets("検索").Range("B6") = Sheets("DB").Cells(i, "E") '役職
Sheets("検索").Range("B7") = Sheets("DB").Cells(i, "F") '生年月日
Sheets("検索").Range("B8") = Sheets("DB").Cells(i, "G") '入社年
Sheets("検索").Range("B9") = Sheets("DB").Cells(i, "H") '勤続年数
End If
Next
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
'「G1」のセルが変更されたら実行する
If Target.Address(False, False) = "G1" Then
Call TEST1 '検索値を絞りこむ
End If
'「G2」のセルが変更されたら実行する
If Target.Address(False, False) = "G2" Then
Call TEST2 '値を検索
End If
End Sub