【脱エクセルVBA初心者】Vlookupで出てしまうエラーをいい感じに回避する方法

★気に入ったらシェアをお願いします!

error

photo credit: via photopin (license)

みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。

脱エクセルVBA初心者向け、請求書を自動で作る際の様々なテクニックについて連載でお伝えしています。

前回の記事はコチラ。

【脱エクセルVBA初心者】Copyメソッドで範囲まるごとを一発でコピペする
脱エクセルVBA初心者向け、請求書を自動で作る際の様々なテクニックについての連載です。。今回は、範囲単位でまるごとコピペをする方法についてお伝えしつつ、請求書作成プログラムを作り込んでいきます。

ほぼ、完成して来ました。

続いてですが、実際に作る請求書に載せる取引先名、郵便番号、住所を「取引先マスタ」から引っ張って来たいので、プログラム内でVlookupを使います。

しかし、「取引先マスタ」に検索値が存在していなかったらエラーが発生しVBAプログラムが停止をしてしまいます。

今回はエクセルVBAでVlookupを使ったときに発生し得るエラーを回避する方法についてお伝えします。

前回までに作ったプログラム

前回までに作成したプログラムはこちらです。

ちょっと長いですがこれまでの集大成なのでご容赦下さい。

内部で使用している関数wsDataSortについてはコチラ

【脱エクセルVBA初心者】データが含まれる範囲全体を一発で取得してSortで並び替え
脱エクセルVBA初心者向けのテクニックとして、CurrentRegionプロパティでデータ範囲を塊で取得して、それをSortメソッドで並び替えをする方法についてお伝えしていきます。

firstDay()に関ししてはコチラの記事をご覧ください。

【脱エクセルVBA初心者】コピペはRangeが効率的なのでその範囲を見つけるプログラム
脱エクセルVBA初心者向け、請求書を自動で作る際の様々なテクニックについてシリーズ。請求データを塊つまりRangeで見つけられれば一気にコピペできるようになります。その「塊」を効率よく見つける方法です。

Vlookupで取引先情報を取得する

今回のお題ですが、請求書の以下の場所に送り先の取引先名、郵便番号、住所を表示していたいと思います。

エクセルVBAで取引先情報を請求書に記載する

取引先名はわかっているとして、それ以外の郵便番号、住所1、住所2を取引先名をキーにして以下「取引先シート」からVlookupで引っ張って来ます。

エクセルVBAで取引先情報を請求書に記載する

エクセルVBAでのVlookupの使い方

エクセルVBAでVlookupを使うときは

WorksheetFunction.VLookup(検索値,範囲,列番号,検索の型)

とします。

今回の場合、WordsheetオブジェクトがwsInvoice、検索範囲となるRangeオブジェクトを

としてrngAccountにセットしたとすると

とすることで、各情報をVlookupで取り出して表示することができます。

エクセルVBAのVlookupで取引先情報を表示

【エクセルVBA入門】マスタからVlookupでデータを取得する方法
エクセルVBAを使ってバラバラの経費精算書データを集約するシリーズの7回目です。今回は、業務で有効なテクニックとしてもう一つ、マスタシートからVlookupでデータを取得してくる方法についてお伝えしていきます。

Vlookupで検索値がない場合

しかし、取引先マスタの取引先名に半角スペースが入っているなど検索がうまくいかなかった場合はどうなるでしょうか?

試しに、取引先マスタの「株式会社ホゲホゲ」を「株式会社 ホゲホゲ」と、ちょっといたずらして半角スペースを入れちゃいます。

これで実行しますと

エクセルVBAのVlookupでエラーが発生

やっぱりエラーが出ちゃいました。

これを回避する方法についてお伝えしなければなりません。

On Error Resume Nextでエラーを無視する

エラーが発生してもそれを無視する魔法の言葉があります。

On Error Resume Next

この一文を入れた以降は、エラーを無視してエラーが発生した次の行から処理を続行します。

これを先ほどのVlookup命令の前に入れてあげますと、プログラムは最後まで実行され、本来エラーが発生している取引先については

エクセルVBAでエラー無視したところはデフォルト表示

というように、郵便番号、住所はデフォルトのままになっています。

でも、ちょっとエラーが発生したかどうかが分かりづらいですね。

このまま取引先に送ってしまわないか心配になっちゃいます。

Errオブジェクト

そんな時のためにErrオブジェクトを使って、もう少しエラーが発生したことをわかりやすくしてあげましょう。

Errオブジェクトには、エラーが発生したときにどんなエラーが発生したのかという情報が格納されます。

これを使ってエラーが発生したときに何らかのアクションを起こすという処理を追加することができます。

Err.Numberでエラーが発生したときにメッセージを表示

ErrオブジェクトのNumberプロパティには初期値は0が格納されていて、エラーが発生するとエラーの種類に応じて0より大きい番号が格納されます。

Errオブジェクト.Number

ですから例えば

とすれば、この構文の前にエラーが発生していればエラーメッセージが表示されます。

エクセルVBAでエラーが発生したらメッセージを表示

ClearメソッドでErrオブジェクトをリセットする

しかし、エラーが発生したときのErrオブジェクトのNumberプロパティは、別のエラーが発生しない限りはプロシージャが終了するまでリセットされません。

従って、今回のプログラムではDo Whileの処理内にありますので、直前でエラーが発生しようがしまいがメッセージが表示されてしまうという仕組みになってしまいます。

そんなときは、ErrオブジェクトのClearメソッドを使ってあげます。

Errオブジェクト.Clear

この命令を入れておくことで、Errオブジェクトを初期状態にリセットできます。

前述のIf文内を

としてあげればエラーメッセージが表示されるたびにエラーがクリアされます。

まとめ

エクセルVBAでVlookupを使ったときのエラーを回避する方法についてお伝えしました。

エクセルVBAの場合は、ユーザーがシートやデータをいじれくれる場合が多いので、人為的なミスが発生する要因がけっこう多かったりします。

ちょっとの文字間違いでもスペースのありなしでもプログラムとしてはエラーが出る要因になりますので、よく作成するVBAプログラムとそれを使う際の状況と業務プロセスなどを予想して、先回りでエラー処理を入れておけると良いですよね。

また機会があれば、その他のエラー処理についてもお知らせできればと思います。

次回はいよいよ脱エクセルVBAの集大成、請求書作成プログラムの仕上げをしていきます。

【脱エクセルVBA初心者】請求書作成マクロの全体おさらいと総仕上げ
脱エクセルVBA初心者向け、請求書を自動で作る際の様々なテクニックについてお伝えしています。過去の記事でお伝えしたテクニックも多く含まれますが、おさらいも含めまして総仕上げの解説をしていきます。

どうぞお楽しみに!

連載目次:【脱エクセルVBA初心者】請求書を自動で作る際のテクニック集

「請求書を自動で作る」というニーズはとても多いですから、その際に便利な機能やテクニックをシリーズにてお伝えしていきます。脱エクセルVBA初心者をターゲットにしています。
  1. 【脱エクセルVBA初心者】新規のワークブックを作成、シートのコピーと名前の変更
  2. 【脱エクセルVBA初心者】確認メッセージを表示させずにワークシートを削除する方法
  3. 【脱エクセルVBA初心者】PDFでの出力とファイル名の指定保存そして閉じる
  4. 【脱エクセルVBA初心者】データが含まれる範囲全体を一発で取得してSortで並び替え
  5. 【脱エクセルVBA初心者】コピペはRangeが効率的なのでその範囲を見つけるプログラム
  6. 【脱エクセルVBA初心者】Copyメソッドで範囲まるごとを一発でコピペする
  7. 【脱エクセルVBA初心者】Vlookupで出てしまうエラーをいい感じに回避する方法
  8. 【脱エクセルVBA初心者】請求書作成マクロの全体おさらいと総仕上げ