Vba worksheetfunction vlookup エラー回避

VBAで中級から上級にステップアップするシリーズの4回目です。今回はVLOOKUPの検索値と戻り値を調べることで、データの取り扱いに配慮する事について解説します。VBAではなく通常のエクセル関数のVLOOKUPがうまくいかない事例を見てから、エラーの回避に取り組んでいきます。

この記事は中級です。
レベルについてはExcel VBAの実力（レベル）を定義してみる 初心者～三段をご参照ください。

目次

普通のエクセル関数のVLOOKUPがうまくいかない例

まずは普通のエクセル関数のVLOOKUPがうまくいかない例を見ていきましょう。

あるはずなのに検索できない

良くあるのが検索されるべき値が、検索範囲内にあるのにVLOOKUPが当たらない場合です。これは大きく2つの原因があります。

1. 文字数を揃えるためのスペースが入っている
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を当てることができました。