エクセル関数やVBAのコードを自動で生成

GPTExcelの使い方はこちら

VBA-14複数のセルに書かれた検索値に対してVLOOKUP関数を使う

スポンサーリンク

お知らせ記事には広告が含まれておりますがExcelのスキルUPに繋がる様コンテンツ自体は手を抜かずに作成しております

最後の課題を用意しました

今回で初心者向けエクセルマクロ学習シリーズとしては完結します。

マクロとはVBAとはから始まり色々な事を勉強してきました。

VBE、オブジェクトとプロパティの関係、メソッド、変数、IF文、ループ、関数等々。

これが最後です。今まで勉強してきたことを使って課題に取り組んでいただきます。

実は課題は今まで勉強した範囲より少し広めの内容なので出来なくて当然の仕様にしています。

自力で勉強してコードを完成させてほしいという思いを込めて少し難しくしてあります。

EnjoyExcel
EnjoyExcel

まだVBAの入り口です。

もっといろいろな事ができるので引き続き勉強してください。

前回の記事

前回は関数の定義とVLOOKUP関数を使ってワークシート関数を勉強しました。

その中でVLOOKUP関数で#N/Aが出た際(エラーの時)マクロが止まらない様対策しました。

本記事での課題

今回の課題です。最後はちょっと難しいかも。

EnjoyExcel
EnjoyExcel
課題

課題をクリアして学習をコンプリートしよう

初心者向けエクセルマクロ学習シリーズ

最後の課題に挑戦

課題としては今まで勉強してきたことで9割は対応可能ですがちょっと背伸びしてるところもあります。

教えてないところもありますがやってみましょう。

データ環境:データシート

データシートは2枚になります。

プロジェクトエクスプローラは以下のようになっています。

データ環境:各シートのデータ

こちらは Sheet1(データベース) という名前のシートです。

これも「なんちゃって個人情報」で作成しているダミーデータです。

各自コードを書くときは名前列とは別に2つ程度の列の情報を使って適当に表を作成してみてください。

件数(行方向)は10件もあれば良いと思います。

こちらは Sheet2(抽出シート) という名前のシートです。

私が用意したデータベースは5,000件です。抽出シートのD列は200件です。

先程も書きましたが同じ量のデータは必要無いと思います。

取り組んでいただく内容

抽出シートにある名前とデータベースの名前が一致したら関連のデータを取り出します。

抽出シートのE、F列の都道府県とカレーの食べ方を取り出すデータを用意してみてください。

ご検討ください
  • 私は「都道府県」と「カレーの食べ方」ですが各自好きなデータを用意してください
  • 抽出シートの実行ボタンで作業を行う様コードを作りこんでみてください
  • 抽出シートの削除ボタンは抽出シートのE、F列を削除する際に使用します

プロシージャとしては2つですね。

実行削除分けて作ってください。

回答

ではコードの話に戻ります。答え載せておきます。

変数に日本語使うのは抵抗あるという人も居ますが私は問題無いと思います。誤作動もしませんので。

私は変数を英語でも書きますが日本語の方が引継ぎが楽な印象があります

何やってるか伝わりやすい事が多いのでメリットもありますよ。実行ボタンのコードです。

1行に収まらないコードが2行にまたがっているところがあります。

コードはそのままコピーしてVBEに貼り付けてください。(長いコードは1行に補正されます)

Sub vlookup()

'変数の定義
Dim 行 As Long
Dim 列 As Long
Dim 抽出行 As Long

'画面更新の停止
Application.ScreenUpdating = False

'データベースの最終行と最終列を確認する
With Sheet1
  行 = .Cells(Rows.Count, 1).End(xlUp).Row
  列 = .Cells(5, Columns.Count).End(xlToLeft).Column
End With

'抽出シートの4列目をループする
For 抽出行 = 6 To Cells(Rows.Count, 4).End(xlUp).Row
  
  '抽出シートの名前がデータベースに居るのか確認。
  If WorksheetFunction.CountIf _
  (Sheet1.Range(Sheet1.Cells(6, 1), Sheet1.Cells(行, 1)), Sheet2.Cells(抽出行, 4)) > 0 Then
  
    '居たらVLOOKUPで結果を抽出
    Cells(抽出行, 5) = WorksheetFunction.vlookup _
    (Sheet2.Cells(抽出行, 4), Sheet1.Range(Sheet1.Cells(6, 1), Sheet1.Cells(行, 列)), _
    8, False)
    
    Cells(抽出行, 6) = WorksheetFunction.vlookup _
    (Sheet2.Cells(抽出行, 4), Sheet1.Range(Sheet1.Cells(6, 1), Sheet1.Cells(行, 列)), _
    10, False)
  
  Else
    '居ない時は「該当なし」と表示させる
    Cells(抽出行, 5) = "該当なし"
    Cells(抽出行, 6) = "該当なし"
  
  End If

Next

'画面更新停止の解除
Application.ScreenUpdating = True

End Sub

削除ボタンのコードです。

Sub clear()

'変数の定義
Dim 行 As Long
Dim 行2 As Long

'抽出シートの都道府県とカレーの食べ方が入力されている列の最終行を認識
行 = Cells(Rows.Count, 5).End(xlUp).Row
行2 = Cells(Rows.Count, 6).End(xlUp).Row

'抽出シートの都道府県とカレーの食べ方が入力されている列の最終行が5より大きかったら・・・
If 行 > 5 And 行2 > 5 Then

  '結果をクリアにする(都道府県とカレー食べ方の抽出結果を消去する)
  Range(Cells(6, 5), Cells(行, 6)).ClearContents
  
End If


End Sub

Application.ScreenUpdatingやclearプロシージャの比較演算子、IF文の中でのAndの使い方は初出しです。

書籍やインターネットで調べながら意味を理解してください。

あえて解説はしません

これからは自分で調べてコードを書いていく事になります。

私が提示したコードが正解ではないので皆様が調べて自分でより良いコードを見つけてください。

そこからまた勉強が始まります。

知識の積み重ねだと思ってやってみてください。

どうしても分からない方は問い合わせフォームより連絡をお願いします。

まとめ

ここまで出来れば書店に行ってもVBA初級編の本なら結構読めるはずです。

厚みのある専門書みたいなものではなく「いけそうな気がする」本を手に取ってください。

本日勉強したこと
  1. IF文でエラー回避できる
  2. エラーの受け皿を用意してあげる
  3. 課題はなんとか頑張ってみて!!
お疲れさまでした

Enjoy Excel

それでも???になったらもう1回勉強し直しましょう。

ネタはなんでも良いです。1回でうまくいくことは無いので繰り返し勉強しましょう。お疲れ様でした。

お疲れ様
お疲れさまでした。

目次に進みましょう。VBAの関連記事や関数、その他色んな情報を用意しています。

タイトルとURLをコピーしました