キーが合致していれば、VLOOKUP関数が使えますが、びったりキーが合わなくて、キーワードで検索してその内容を別のシートから情報を持ってくるというシチュエーションがあると思います。そのマクロの説明です。
設定条件
全体の得意先の情報が、”得意先テーブル.xlsx”というファイルにあるとします。
今回、”魚屋住所.xlsx”という魚屋だけのファイルが今回入手できました。ところが、得意先テーブル.xlsxには、有限会社みかわ屋とか、正式名称の記載がありますが、今回、入手した魚屋住所.xlsxは、みかわ屋という名称だけで、VLOOKUP関数が使えない状況です。
得意先テーブル.xlsxには、郵便番号、県名、住所のフィールドがありますが、歯抜けとなっており、今回、魚屋住所.xlsxに郵便番号、県名、住所のフィールドが正しいものが入手できたので、得意先テーブル.xlsxの内容を最新のデータに置き換えたいというように考えました。
行数は、得意先テーブル.xlsxが10,000行に対して、魚屋住所.xlsxが300行というようなイメージです。
シートイメージ
”得意先テーブル.xlsx”
”魚屋住所.xlsx”
”魚屋住所.xlsx”のデータを”得意先テーブル.xlsx”に入れたい。
マクロ内容
まず、”魚屋住所.xlsx”から、ひとつのデータを、”得意先テーブル.xlsx”に移動するマクロを作りましょう。
ここで、マクロ名は、得意先テーブルへ() としました。
On Error GoTo エラーの処理 → Exit Sub エラーの処理: は、エラーの時には、MsgBox “見つかりません” を表示するとしました。
次の入力セル行は、今アクティブである（選択しているセル）の行数を調べております。ActiveCell.Row
つまり、今選択しているセルで検索したいということです。
行数がわかれば、”魚屋住所.xlsx”から検索したい文字列は、A行にあるので、変数の検索文字は、Cells(入力セル行, 1) でマクロに覚えてもらいます。
ついでに、郵便番号、県、市は、同じセル行のB列（２）、C列（３）、D列（４）にあるので、それぞれ、変数として覚えてもらいます。
”魚屋住所.xlsx”のデータをマクロに覚えてもらったら、”得意先テーブル.xlsx”に切り替えて、検索します。
検索は、Cells.find(なんちゃら ですが、「What:=検索文字」としております。 ここで、変数名を検索文字以外の変数にした場合には、ここを変更してください。
検索がヒットすると、検索されたセルがアクティブセルとなるので、ひとつ右にアクティブセルを移動 ActiveCell.Offset(0, 1).Range(“A1”).Select
して マクロに覚えてもらった郵便番号を入力します。今あるセルに入力するのは、 ActiveCell.FormulaR1C1 = 郵便番号 と記載します。
もうひとつ右にアクティブセルを移動 して、県、同様に市を入力します。
最後に、”魚屋住所.xlsx”に戻って、ひとつ下の行に移動しておいて、次の検索ができる準備を行います。 ActiveCell.Offset(1, 0).Range(“A1”).Select
Sub 得意先テーブルへ()
On Error GoTo エラーの処理
入力セル行 = ActiveCell.Row
検索文字 = Cells(入力セル行, 1)
郵便番号 = Cells(入力セル行, 2)
県 = Cells(入力セル行, 3)
市 = Cells(入力セル行, 4)
Windows("得意先テーブル.xlsx").Activate
Cells.Find(What:=検索文字, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, MatchByte:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = 郵便番号
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = 県
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = 市
Windows("魚屋住所.xlsx").Activate
ActiveCell.Offset(1, 0).Range("A1").Select
Exit Sub
エラーの処理:
MsgBox "見つかりません"
End Sub繰り返し設定
上記一つの検索が成功すれば、そのマクロを繰り返して一気に処理してしまいましょう。
注意）マクロの変な動きが起きたときのために、一気にマクロを動かす場合には、必ず、ファイルを保存しておいてください。そのひと手間が人生を助けます。
ここでは、main() というマクロ名にしました。
内容は、Do While – Loop で回数を１０回の場合としました。回数を変更したい場合には、Do While i < 10 の１０を変更してください。
Sub main() i = 0 Do While i < 10 i = i + 1 得意先テーブルへ Loop End Sub
コメント