VLOOKUPをVBAラベルを活用してデータ検索・修正をする方法

パソコン難しい Excel-VBA
スポンサーリンク

こんにちは、とりっぷぼうるです。

元々エクセルとは全く縁のない生活をしていましたが、人員不足などで少しでもデータ入力作業や集計作業を効率化するしか方法が無く、仕方がなくエクセルに取り組み始まました。

なぜならば、大きな会社ならば専用ソフトやクラウド上でデータ集計や管理を行うのでしょうけど、小さなところではそのようなお金もないのでエクセルで解決するしか方法が無いのですよね。

このような理由からエクセルに取り組み始めましたので、当然ながらエクセルの専門知識もなく、学校に通うこともできずにただ単にエクセルの書籍とにらめっこしながら取り組んでいます。

波乗りアヒル
波乗りアヒル

でも書籍のままだと実用的でないことが多く、VBAコードを組み合わせてみたら動いた!という実用例・体験談を書いています。

Excel-VBAの専門学校通学や・資格があるわけではありませんので、あくまでエラーにならずに動いてくれているVBAコードになりますので、掲載のVBAコードが同じように動く保証はできません。
実際に動いているエクセルは2009と2016です。

ラベル(Label)にVBAコードを記述する

ボウリング成績表でも使っているユーザーフォームに配置した文字を表示するための「ラベル(Label)」にも、実はVBAコードを記述してプログラムを実行することができます。

VBAコードを実行するのはコマンドボタン(CommandButton)だけではないのです。

一般的に「表の中から特定の指定したセルの値に応じて目的のデータを検索する」場合、VLOOKUP関数がとても有名ですね。

このVLOOKUP関数はそのままエクセルVBAで使うことはできません。

その為にVBAではFindメソッドを使ってVLOOKUP関数と同じことができるようになります。

そこで「ボウリング成績表選手登録方法~LTB個人リーグ編」では、ユーザーフォームに配置した参加選手名を表示している「ラベル(Label)」に、直接VBAコードを記述して【修正登録】ボタン(コマンドボタン)を押すと検索したいデータ(名前)を選択して、表に記載されている会員番号・名前・AVE・性別を変更修正できるようにしています。

下の図の説明に関しては下記ページに記載していますので、ここでは省略します。

ボウリング成績表選手登録方法~LTB個人リーグ編
エクセルで作成しているボウリングリーグ成績表を自動化で、リーグ戦参加者を新規登録・修正・削除する専用フォームの使い方

LTB選手登録

ラベルに選手番号とFindメソッドで見つけて、修正登録で書き換える

VBAコードが反映されるワークシートです


画像スペースの都合で、16行目まで(AA16まで)切り取って貼り付けています。実際は42行目まであります。(AA42)

では、実際にはラベル(Label44)にはこのようなコードを記述しています
Private Sub Label44_Click()
Application.ScreenUpdating = False
Range(“AA1”).Value = “1”
msg = MsgBox(“選手1を修正しますか?”, Buttons:=vbYesNo + vbQuestion)
If msg = vbYes Then
Dim mycell As Range
Set mycell = Range(“AA3:AA42”).Find(What:=Range(“AA1”).Value, LookAt:=xlWhole)
If Not mycell Is Nothing Then
mycell.Select
UserForm1.TextBox1.Value = mycell.Offset(0, 1).Value
UserForm1.TextBox2.Value = mycell.Offset(0, 2).Value
UserForm1.TextBox3.Value = mycell.Offset(0, 4).Value
Else
End If
End If
Application.ScreenUpdating = True
End Sub

実際に記述しているVBAコードをそのまま書いているので、VLOOKUP関数をVBAのFindメソッド以外のコードもありますが、赤い下線の部分が表の中から探す部分です。

一応記述したVBAコードを順番に説明します。

  1. 「ラベル44をクリックしたら実行する」というマクロの記述を開始
  2. 画面のちらつき(無駄な移動)を停止する
  3. 検索値として選手1なので「1」をセル「AA1」に転記する
  4. メッセージで「選手1を修正しますか?」と表示し、「はい」「いいえ」を選択させる
  5. 「はい」を選択したら下記を実行する
  6. 選手番号が変わるので、変数として「mycell」を宣言する
  7. セルAA3からAA42の範囲でセルAA1と同じ値(選手番号)があるセルを検索して、見つけたセルを変数mycellに格納する
  8. 変数mycellの値(選手番号)が見つからない(Nothingでない場合)、要するに見つけた場合はIfステートメントの開始(もし見つけたらということになる)
  9. 見つけたセル「mycell」を選択する(ここ例では表の中の選手番号1はセルAA3にある)
  10. ユーザーフォーム1のテキストボックス1に「mycell」の同じ行で右1列目(右隣)のセルの値を転記する(会員番号)
  11. ユーザーフォーム1のテキストボックス2に「mycell」の同じ行で右2列目のセルの値を転記する(名前)
  12. ユーザーフォーム1のテキストボックス3に「mycell」の同じ行で右4列目のセルの値を転記する(AVE)
  13. そうでない場合(「はい」ではなく「いいえ」を選択した場合)
  14. Ifステートメントの終了(見つける作業をしない)
  15. Ifステートメントの終了(メッセージ終了)
  16. 画面のちらつき停止を解除する
  17. マクロの記録終了

これで、名前(Label)をクリックするとその選手を表の中から探して、見つけたら「会員番号」「名前」「AVE」を見つけてユーザーフォームに表示するようになります。

ちょっと大変ですが

この作業を繰り返し選手分(40名)をラベル番号を変えながらコードを書いていけば、出来上がります。

これで修正したい表のデータが取り出せたので、次は書き換えです。

修正登録ボタンで表のデータを書き換える

それでは、この表示されたデータの書き換えを【修正登録】ボタン(コマンドボタン)でできるようにしましょう。

では、実際にはコマンドボタン(CommandButton2)にはこのようなコードが書いてあります。
Private Sub CommandButton2_Click()
If UserForm1.TextBox1.Value = “” Or UserForm1.TextBox2.Value = “” Or UserForm1.TextBox3.Value = “” Then
MsgBox “修正データが自動転記されていません”
End If
msg = MsgBox(“修正実行しますか?”, Buttons:=vbYesNo + vbExclamation)
If msg = vbYes Then
ActiveCell.Offset(0, 1).Value = UserForm1.TextBox1.Value
ActiveCell.Offset(0, 2).Value = UserForm1.TextBox2.Value
ActiveCell.Offset(0, 4).Value = UserForm1.TextBox3.Value
If OptionButton1.Value = True Then
ActiveCell.Offset(0, 3).Value = “1”
ElseIf OptionButton2.Value = True Then
ActiveCell.Offset(0, 3).Value = “2”
End If
For i = 44 To 83
With UserForm1.Controls(“Label” & i)
.Caption = Cells(i – 41, 29)
End With
Next i
For j = 84 To 123
With UserForm1.Controls(“Label” & j)
.Caption = Cells(j – 81, 31)
End With
Next j
End If
End Sub
  1. 「コマンドボタン2をクリックしたら実行する」というマクロの記述を開始
  2. もしユーザーフォーム1のテキストボックス1または、ユーザーフォーム1のテキストボックス2または、ユーザーフォーム1のテキストボックス3が空欄だったら
  3. メッセージで「修正データが自動転記されていません」と表示する
  4. 空欄の時のIfステートメント終了
  5. 各テキストボックスが入力されていたら、メッセージで「修正実行しますか?」を「はい」「いいえ」で選択させる
  6. 「はい」を選択したら
  7. 選択されているセル(ラベルを押した時に選手番号を検索した時にすでに選択しています)の同じ行の右に1列隣のセルに(会員番号)ユーザーフォーム1のテキストボックス1に入力されている値(書き換えた会員番号)を転記する(上書きして書き換える)
  8. 選択されているセル(ラベルを押した時に選手番号を検索した時にすでに選択しています)の同じ行の右に2列隣のセルに(選手名)ユーザーフォーム1のテキストボックス2に入力されている値(書き換えた選手名)を転記する(上書きして書き換える)
  9. 選択されているセル(ラベルを押した時に選手番号を検索した時にすでに選択しています)の同じ行の右に4列隣のセルに(会員番号)ユーザーフォーム1のテキストボックス3に入力されている値(書き換えたAVE)を転記する(上書きして書き換える)
  10. もし(性別選択)オプションボタン1が選択されている場合
  11. 選択されているセル(ラベルを押した時に選手番号を検索した時にすでに選択しています)の同じ行の右に3列隣のセルに(性別)半角数字「1」を転記する(上書きして書き換える)
  12. そうではなく、オプションボタン2が選択されている場合
  13. 選択されているセル(ラベルを押した時に選手番号を検索した時にすでに選択しています)の同じ行の右に3列隣のセルに(性別)半角数字「2」を転記する(上書きして書き換える)
  14. オプションボタンに関するIfステートメント終了

15行目以下は、書き換えた内容が反映されているか確認できるために、再度各ラベルを再表示させる為のコードです。

これは、「複数のラベル名を一括で複数のセルの値に書き換える」ページで書いてあることと一緒になるので、ここでは省略します。

複数のラベル名を一括で複数のセルの値に書き換える方法
ユーザーフォームに配置したラベルの名前をセルの値から順番に読み取って書き換えたい。1個ずつやるのは面倒なので変数を使ってまとめて一括で書き換えてしまいましょう。

これで、簡単に言うと、データ修正ができます。

この方法を使いこなせるようになると、会員住所録などでも使えるようになるので、とても重宝しますよ。

コメント