キャッチ画像にもある様にVBAのシリーズとしては完結編になります。もう少しお付き合いください。よろしくお願い致します。
前回はワークシート関数のVLOOKUPを使ってみようという記事でしたね。
enjoyexcel.hatenablog.com
でも検索対象となるデータがデータベースに無い時にはエラーになってしまうという事で不完全なコードでした。エラーが出る原因からすると今後も起こる可能性があります。
ではどうしたら良いでしょうか？エラーが出るときと出ないときの条件がほとんど明確なので条件分岐を使って考えてみましょうか。
データがあるときはVLOOKUPを実行しデータが無い時はVLOOKUPを実行せず「データが無いよ」って言ってあげれば良さそうな気がします。
ってことはやっぱり条件分岐が使えそうな感じがしてきますね。ＩＦ文採用しましょう。
続いてデータの有無を確認するにはいろいろな方法がありますが今回もワークシート関数を使いましょう。ＣＯＵＮＴＩＦ関数を使います。
ワークシート関数のＣＯＵＮＴＩＦは
ＣＯＵＮＴＩＦ（第１引数,第２引数）という形です。
第１引数
関数実行時の対象となるデータベースの範囲です。
第２引数
データベースの中で検索したい文字です。
ＣＯＵＮＴＩＦ（第１引数,第２引数）＞０ という条件にしたらデータベースの中に第２引数の情報が居るのか確認出来ますね。
上記をＩＦ文の先頭にしてＴＲＵＥだったらＶＬＯＯＫＵＰ、ＦＡＬＳＥだったら”該当なし”等の言葉を出してあげればエラーは出ないです。
前回のワークシートのネタを元にコードを組んでみました。
これでエラーが出ない様になってます。
では仕上げです。課題としては今まで勉強してきたことでほとんどは対応可能ですがちょっと背伸びしてるところもあります。教えてないところもありますがやってみましょう。
データシートは２枚になります。
プロジェクトエクスプローラは以下のようになっています。
こちらは Sheet1（データベース） という名前のシートです。
これも「なんちゃって個人情報」で作成しているダミーデータです。
各自コードを書くときは名前列とは別にあと２つの列の情報を適当に作成してみてください。件数は１０件もあれば良いと思います。
こちらは Sheet2（抽出シート） という名前のシートです。
データベースは５０００件です。抽出シートのＤ列は２００件です。私が用意したデータと同じ量は必要無いと思います。
課題
抽出シートにある名前とデータベースの名前が一致したら抽出シートのＥ、Ｆ列に都道府県とカレーの食べ方を引っ張ってくるという仕様のデータを用意してみてください。
抽出シートの実行ボタンで作業を行う様コードを作りこんでみてください。
抽出シートの削除ボタンは抽出シートのＥ、Ｆ列を削除する際に使用します。
ってことはプロシージャとしては２つですね。実行と削除を分けて作ってください。
ヒント
・コードは標準モジュールに書きましょう。
・For～Next でループさせるのは抽出シートのＤ列の情報です。
・ループは５行目、もしくは６行目から最終行とします。
・データベースの情報についても最終行と最終列を認識する必要があります。
・抽出シートの名前がデータベースの中に居るのか確認しましょう。名前が居たらＶＬＯＯＫＵＰを行い居なかったら”該当なし”を表示させましょう。
・都道府県とカレーの食べ方を抽出するのでＶＬＯＯＫＵＰは２つ必要です。
・シート間の情報を扱いますのでRangeやCellsの前にSheetを指定する必要があります。（これはまだ紹介していません）
・削除データは単純に範囲を決めて削除という形でもＯＫです。
いかがでしょうか。前の記事に戻っていただき探すのも良しYahooさんやGoogleさんに頼るも良しです。考えながら書いてテストするという事が重要です。こんな感じで実務ではやりたい事を整理してコードに変換し自力で書いてテストするという事が求められます。
コード書いてるだけでしょ？って思っている人も居るかもしれませんが実際は依頼された仕事を理解してどうやって使うのか考えながら設計図を作りコードを書いてテストし依頼者へ納品する。納品後使用感をみてもらい修正を行うところまでやり通すという事がほとんどだと思います。
スキルの無い人の依頼は「どんな使い方したい」とかアウトプットのイメージなんて無くただ「楽にして」です。伝わりにくいのですがこちらが考える事は結構多いです。
ではコードの話に戻ります。
答え載せておきます。変数に日本語使うのは抵抗あるという人も居ますが私は問題無いと思います。誤作動もしませんので。私は変数を英語でも書きますが日本語の方が引継ぎが楽な印象があります。何やってるか伝わりやすい事が多いのでメリットもありますよ。
実行ボタンのコードです。
Sub vlookup()
'変数の定義
Dim 行 As Long
Dim 列 As Long
Dim 抽出行 As Long
'画面更新の停止
Application.ScreenUpdating = False
'抽出シートの４列目をループする
For 抽出行 = 6 To Cells(Rows.Count, 4).End(xlUp).Row
'データベースの最終行と最終列を確認する
With Sheet1
行 = .Cells(Rows.Count, 1).End(xlUp).Row
列 = .Cells(5, Columns.Count).End(xlToLeft).Column
End With
'抽出シートの名前がデータベースに居るのか確認。
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
'抽出シートの都道府県とカレーの食べ方が入力されている列の最終行が５より大きかったら・・・
If 行>5 and 行2>5 then
'結果をクリアにする（都道府県とカレー食べ方の抽出結果を消去する）
Range(Cells(6, 5), Cells(行, 6)).ClearContents
End If
End Sub
vlookupプロシージャの Application.ScreenUpdating や clearプロシージャの比較演算子、ＩＦ文の中でのAndの使い方は興味がある方は調べてみてください。そこからまた勉強が始まると思いますが知識の積み重ねだと思ってやってみてください。
長くなりましたが以上となります。
ここまで出来れば書店に行ってもＶＢＡ初級編の本なら結構読めると思います。厚みのある専門書みたいなものではなく「いけそうな気がする」本を手に取ってください。
それでも？？？になったらもう１回勉強し直しましょう。ネタはなんでも良いです。１回でうまくいくことは無いので繰り返し勉強しましょう。
この時期にこういった記事を書いたのは自分なりに意味があって８月の連休をターゲットにしているます。そこまでで下地を作ってもらって８月の連休でコードをたくさん書いてもらえると連休明けから皆様の仕事が片付くのでは？という思いがあります。
私事ですが７月には社内でＶＢＡの勉強会も予定しています。募集をしたら思ったより集まっていただきました。他拠点の方にもオンラインで聞いていただく事になっているのでしっかりやらなきゃなと思っています。このブログをベースに勉強会をやろうと思っています。
では次からはＶＢＡかもしれないし関数やピボットテーブルの記事にするのか全く決めていませんが不定期でブログを書いていきます。お疲れ様でした。