顧客番号や商品番号を入力すると名前・電話番号など欲しいデータを取り出してくれるのでいつも使っているVLOOKUP関数をどうしてもVBAでやらなくてはいけない事ありませんか？
VLOOKUP関数は、あらかじめ使用想定されるセルに設定しなくてはいけないので、範囲が決まっていない時にはVBAでやるしかないですよね。
VLOOKUP関数の代わりにExcel-VBAのFindメソッドをマスターすれば、簡単なデータ検索はもちろん、実用的な住所録のデータ修正までできるようになりますよ。
VLOOKUP関数でデータ検索
そもそもVLOOKUP関数をどのような時に使うのでしょうか？
先ほど書いた通り、住所録で顧客番号を入力すると名前・住所などを見つけて表示させる時に使っています。
下の図で説明するとExcel-VBAがまだ使いこなせてない時に作った住所録です。
A列に【顧客番号】、B列に【名前】・・・G列に【生年月日】と順に入っています。
2行目が空欄になっている理由はセルA2に顧客番号を入力するとセルB2に見つけた名前、セルC2にフリガナ、・・・と表示されるようにしています。
実際にセルにはこのようにVLOOKUP関数を使ってセルB2にはこのような数式を書いています。
=VLOOKUP(A2,A4:G8,2,0)
セルA2に顧客番号を入力すると、A4:G8の範囲内からA2の値と完全に一致する値を見つけたら、その行の2列目の値をで表示する。
2列目は名前なのでB2に名前が表示されます。
これを繰り返してセルC2・・・とVLOOKUP関数を入れていきます。
住所録としては、ただ単に顧客番号から顧客を見つけるだけなのです。
もちろん顧客番号が分かっているので、マウスでスクロールすれば見つけることができるのでスクロールする手間を省いただけなのですが、顧客の住所が変更になった時はスクロールして住所を書き換えるのであまり意味はないと思いませんか？
見つけ出したデータをその場で修正して元のデータと差し替える修正フォームが欲しくなりませんか？
そこでVLOOKUP関数の代わりにExcel-VBAのユーザーフォームを使えば、このような手間のかかる作業から解放され問題が解決できます。
Excel-VBAでデータを検索する
VLOOKUP関数の代わりにExcel-VBAでやる方法ですが、基本的にVLOOKUP関数を使う時の考え方と一緒です。
見つけたい値を指定したセルに入力すると、表の範囲の中から見つけ出し表示してほしい同じ行の列〇〇右のセルの値を取得する、または列〇〇左のセルも可能なので、以下の事は最低限やりたい項目ですよね。
- 探したい顧客番号を入力する
- 見つけた顧客情報を表示（名前や住所などの住所録データ全て）
- 変更したい項目（住所など）を書き換える
- 書き換えた項目（住所など）を元のデータと差し替える
では、データ検索をするVBAコードFindメソッドを使いましょう。
Findメソッドを使ってデータ検索
Findメソッドは、指定したセルの範囲から値を検索して、値を見つけたらRangeオブジェクトで返してくれます。
また、見つからなかった場合はNothingで返してくれます。
住所録のワークシートはわざわざ改めて作成するのではなく、もちろんそのまま使ってVLOOKUP関数の代わりにExcel-VBAを使います。
先ほどのVLOOKUP関数を使っていた時と同じですが、セルB2からG2まで入力していた数式が必要ないって事ですね。
VLOOKUP関数の代わりにExcel-VBAを使ってコードを説明いたします。
下の画像は実際に使っている住所録検索してリストを修正するユーザーフォームで、顧客番号を入力して【検索】ボタンを押すと名前以下全ての項目を見つけ出して、テキストボックスに表示します。
そして修正したい項目があれば書き換えて【修正実行】ボタンを押すと、見つけた行と同じセルにデータを書き換えるようになっています。
【検索】ボタンを押すとVLOOKUP関数と同様に、名前をセルB2へ、フリガナをセルC2へ・・・と生年月日まで全て転記します。
Range(“A2”).Value = TextBox1.Value
Dim mycell As Range
Set mycell = Range(“A4:A8”).Find(What:=Range(“A2”).Value, LookAt:=xlWhole)
If Not mycell Is Nothing Then
Range(“B2”).Value = mycell.Offset(, 1).Value
Range(“C2”).Value = mycell.Offset(, 2).Value
Range(“D2”).Value = mycell.Offset(, 3).Value
Range(“E2”).Value = mycell.Offset(, 4).Value
Range(“F2”).Value = mycell.Offset(, 5).Value
Range(“G2”).Value = mycell.Offset(, 6).Value
Else
MsgBox “未登録番号です”
End If
End Sub
- 【検索】ボタン（CommandButton1）をクリックすると
- セルA2に顧客番号を転記する（TextBox1）
- 変数mycellを宣言する
- セルA4からA8の範囲でセルA2と同じ値（顧客番号）があるセルを検索して、見つけたセルを変数mycellに格納する
- 変数mycellの値（顧客番号）が見つからない（Nothingでない場合）➡要するに見つけた場合はIfステートメントの開始（もし見つけたら・・・）
- 見つけたセルの１つ右にあるセル（名前）をセルB2に転記する
- 見つけたセルの２つ右にあるセル（フリガナ）をセルC2に転記する
- 見つけたセルの３つ右にあるセル（性別）をセルD2に転記する
- 見つけたセルの４つ右にあるセル（郵便番号）をセルE2に転記する
- 見つけたセルの５つ右にあるセル（住所）をセルF2に転記する
- 見つけたセルの６つ右にあるセル（生年月日）をセルG2に転記する
- 見つからなかった場合
- 「未登録番号です」とメッセージを表示する
- Ifステートメント終了
- マクロ終了
これでVLOOKUP関数の代わりにExcel-VBAを使ってコードを検索することができるようになりましたね。
これが基本的なFindメソッドを使ったデータ検索で、このままでは見つけただけなので、ユーザーフォームのテキストボックスに表示させましょう。
検索済みデータをテキストボックスに表示する
Range(“A2”).Value = TextBox1.Value
Dim mycell As Range
Set mycell = Range(“A4:A8”).Find(What:=Range(“A2”).Value, LookAt:=xlWhole)
If Not mycell Is Nothing Then
mycell.Select
With UserForm1
For i = 2 To 9
UserForm1.Controls(“TextBox” & i).Value = mycell.Offset(, i – 1).Value
Next i
End With
Range(“B2”).Value = TextBox2.Value
Range(“C2”).Value = TextBox3.Value
Range(“D2”).Value = TextBox4.Value
Range(“E2”).Value = TextBox5.Value
Range(“F2”).Value = TextBox6.Value
Range(“G2”).Value = TextBox7.Value
Else
MsgBox “未登録番号です”
Exit Sub
End If
End Sub
- 【検索】ボタン（CommandButton1）をクリックすると
- セルA2に顧客番号を転記する（TextBox1）
- 変数mycellを宣言する
- セルA4からA8の範囲でセルA2と同じ値（顧客番号）があるセルを検索して、見つけたセルを変数mycellに格納する
- 変数mycellの値（顧客番号）が見つからない（Nothingでない場合）➡要するに見つけた場合はIfステートメントの開始（もし見つけたら・・・）
- 見つけたらmycellを選んだ状態にしておいて
- Withステートメント開始、ユーザーフォーム1を選び
- 変数「i」を宣言して、2から9まで繰り返しを行う
- ユーザーフォーム1に配置しているテキストボックス「i」（TextBox2～TextBox9まで）に、選んだ状態にしておいたmycell（見つけた顧客番号のセル）の同じ行の、変数「i」-１列目のセルの値を転記する
- ９の作業を繰り返す（これで名前～e-mailまでが各テキストボックスに表示されます）
- Withステートメント終了
- 見つからなかった場合
- 「未登録番号です」とメッセージを表示する
- Ifステートメント終了
- マクロ終了
これでVLOOKUP関数の代わりにExcel-VBAを使ってデータ検索、テキストボックスに表示ができました。
今回は、住所録修正フォームを参考にお話しさせていただきました。
「住所録修正フォーム作成ページ」では、この続きのデータ修正して書き換えまで紹介しています。
