そんなに詳しくはないのだが、うまくいったので備忘録として残す。
シート1にはA列に商品名をピックアップしたリストがある。シート2にはすべての商品と在庫数の一覧がある。一覧表の範囲には"List1"という名前がつけてある。
このような条件で、いちいち数式を入力せずに、シート1の商品ごとにシート2の在庫数が表示されるようにしたい。
セルに数式を入れるだけなら、
= VLOOKUP(A2, シート2!List1, 2, FALSE)
でとりあえず結果は出る。
特注品とかList1に商品名がないとエラーになるので、もうちょっと足して
= IF ( ISNA ( VLOOKUP(A2, シート2!List1, 2, FALSE )), "", VLOOKUP(A2, シート2!List1, 2, FALSE))
とやればよい。範囲に名前をつけていなければ、"A15:C1200" とかいちいち入るのでけっこう式が長くなるので大変。かっこ抜かしそうだし。
ところがこのシート1は別のソフトから出力したテキストデータをインポートしてできるシートなもんだから、あらかじめ式を入れておくことができない。マクロで数式を入れる方法もあるのだが、やたら数式ばっかりというのもどうかと。
そこで今回はシート1の商品名に対して、ひとつずつシート2を検索して在庫数をシート1のセルに入れるということをVBAでやってみた。
結論から言えばポイントは２つ。ひとつはExcelのワークシート関数をVBAで使うにはどうするか。もうひとつはエラーがあった場合の処理をどうするか。
ワークシート関数をVBAで使う場合は、Application.WorksheetFunction.Vlookup()のように書けばよい。引数の使い方はほぼ同じだが、検索範囲の指定は注意が要る。こっちはすぐに分かった。
ところがエラーが発生した場合の処理、たとえばシート2に検索値が見つからなかった場合。これはうちの会社の場合、あるメーカー向けに特別な型番をつけた商品があり、それを探そうとするとエラーになる。
セルに数式を入れた場合、結果セルには"#N/A"と表示される。「結果なし」ってこと。0ではないところがうらめしい。そこでセルの場合はISNA関数を使って、#N/Aが返ってきたときの処理を入れるわけだ。
今回VBAではどうにもできなかった。というのはセルに式を入れる要領でいろいろやった結果、どうしてもエラーになった箇所で止まってしまうのだ。ISNAに渡らないのだ。
いろいろ探し回ってみたが、それらしいことを書いているサイトの通りにやって見てもダメ。ひどいのははっきり書かないで「そのぐらい自分で調べろ」みたいな言い方。ホントに知ってんのか？と思う。
あっちこっちの書き込みを参考に試してみた結果、どうやら「エラーで止まらないようにした上で、エラー番号で判断する」という方法が有効らしい。
以下の例では、シート1のA2から何行か商品名のコードが入っている。シート2の名前つき範囲"List1"の2列目に在庫数データがあるとする。マクロはシート1で実行すること。
Sub 在庫数検索()
Dim SerchName As String
Dim SerchArea As Range
Dim Results As Variant
'初期設定
Range("A2").Activate
ItemCode = Range("A2").Value
i = 0
'検索範囲の設定（ポイント1）
Set SerchArea =Worksheets("シート2").Range("List1")
'商品コードが空になったら終わり
Do Until ItemCode = ""
'エラーになっても続行する（ポイント2-1）
On Error Resume Next
'商品コードに該当するデータを探し、Resultsに入れる
ItemCode = ActiveCell.Offset(i, 0).Value
Results =Application.WorksheetFunction.VLookup(ItemCode, SerchArea, 2, False)
'該当するデータがないとエラーになるための処理、エラーなら空欄にする（ポイント2-2）
If Err <> 0 Then Results = ""
ActiveCell.Offset(i, 1) = Results
i = i + 1
Loop
End Sub
シート2も実は別のソフトから吐き出したタブ区切りデータをワークシートに取り込み、ソートしたりデータの種類ごとに分類して複数の範囲名をつけるようになっている。範囲名で指定すれば、行数が変わっても式は同じでいける。
ポイントは検索範囲の指定方法（ポイント1）と、エラーが起きた場合の対処（ポイント2-1、2-2）だ。
正当な方法かどうかは自信がない。とりあえず走るのでＯＫというレベルだが、またひとつ経験値が上がったと言えるかも。別法があればご教示いただきたい。
