VBAで中級から上級にステップアップするシリーズの4回目です。今回はVLOOKUPの検索値と戻り値を調べることで、データの取り扱いに配慮する事について解説します。VBAではなく通常のエクセル関数のVLOOKUPがうまくいかない事例を見てから、エラーの回避に取り組んでいきます。
この記事は中級です。
レベルについてはExcel VBAの実力(レベル)を定義してみる 初心者~三段をご参照ください。
目次
普通のエクセル関数のVLOOKUPがうまくいかない例
まずは普通のエクセル関数のVLOOKUPがうまくいかない例を見ていきましょう。
あるはずなのに検索できない
良くあるのが検索されるべき値が、検索範囲内にあるのにVLOOKUPが当たらない場合です。これは大きく2つの原因があります。
- 文字数を揃えるためのスペースが入っている
- 書式が合っていない
順に解説します。
文字列の長さを揃えるためにスペースが挿入されていることがある
イメージとしては下記のような感じです。
一見、この会社で使っている商品コードは4桁の英数字であわら割れているように見えます。しかし、数式バーをクリックしてみると空白の文字列がある事が分かります。
何故このようなことが起きるかというと、このデータは基幹システムからcsvファイルというテキストファイルでダウンロードしたモノをエクセルに保存し直したデータだからです。この会社では商品コードは10桁の商品も存在します。基幹システム内ではすべての商品コードを10桁に揃えて管理している為、4桁の商品コードでは基幹システム内ではスペースで埋めて10桁にしていた、と言うわけです。
このような理由で基幹システムからダウンロードしたcsvファイルをエクセルで開くと、空白のスペースがそのまま空白で残っているのでした。
この状態でVLOOKUPを当ててもうまくデータが検出できません。
販売単価をVLOOKUPで貼り付けようとしましたが、失敗しました。この販売単価の表は人が手作業で作成したので、商品コードに余計なスペースが入っていません。
文字数を揃えるためのスペースの削除を行う
通常のエクセル作業としては、商品コードの部分に対して、データ⇒区切り位置を選択し、区切り文字にスペースを選択すれば、スペースが無くなり、VLOOKUPを当てることができます。
VBAではスペースを空白の文字列""で置き換える事で簡単に実現できます。配列をマスターしている人であればSplit関数で空白を区切り文字にして0番目の要素で置き換えていっても構いません。
Sub 空白の文字列を削除してからVLOOKUPする() Dim i As Long For i = 2 To 7 Cells(i, 1) = Replace(Cells(i, 1), " ", "") Cells(i, 2) = Application.WorksheetFunction.VLookup(Cells(i, 1), Range(Cells(1, 4), Cells(7, 5)), 2, False) Next i End Sub
できました。もし一覧表の中に検索すべき値が内可能性がある場合はエラーになるのでOn Error Resume Next とOn Error GoTo 0でVLOOKUP部分を挟みましょう。
値の書式が異なる
他にVLOOKUPが当たらない可能性があるのは、値の書式が異なる場合です。上の例で商品コードが文字列のみで成り立つ場合はいいのですが、数値のみで成り立っている商品コードがある場合は注意が必要です。片方が数値、片方が文字列の場合があるためです。
文字列に揃える
この場合は文字列に置き換えます。Cstr関数という関数で文字列に置き換える事ができるのですが、ココが要注意点です。エクセルさんのお気遣いにより、セル内の値が文字列であっても、数値として捉えられる場合は数値として捉えられてしまいます。よって、①セルの書式を標準から文字列に置き換える、②セルの値を文字列に置き換える、という2ステップが必要です。VBAのコードはこのようになります。
Sub 空白の文字列を削除してからVLOOKUPする() Dim i As Long For i = 2 To 7 Cells(i, 1).NumberFormat = "@" Cells(i, 1) = CStr(Cells(i, 1)) Cells(i, 2) = Application.WorksheetFunction.VLookup(Cells(i, 1), Range(Cells(1, 4), Cells(7, 5)), 2, False) Next i End Sub
無事にVLOOKUPを当てることができました。
今後の書き方
今回はVLOOKUPの解説のためにVLOOKUPの直前でスペースを取り除いたり、セルの書式と値を文字列に変更しました。しかし、これはVLOOKUPの有無にかかわらず、商品コードのような値の時にはプログラムの冒頭でまとめて行ってしまうのが良いと思います。
<関連記事>
中級から上級へステップアップシリーズ