表の中から同じ検索条件で条件に合うデータだけを順に取り出す方法

エクセルVBAデータの操作 VBA

スポンサーリンク


成績表や住所録などの表の中から条件に合うデータだけ取り出してリストアップして一覧表を作りたいって事ありませんか?

点数順に並び替えたり、科目別に並び替えたりするにはエクセルのフィルター機能を使って「検索」しますよね。

できれば、ボタン一つで一瞬で男性リスト・女性リストとかできると便利なので、Excel-VBAでユーザーフォームを使って簡単に一瞬でリストアップしちゃいましょう。
スポンサーリンク


スポンサーリンク

FindNextメソッドを使おう

FindNextメソッドは、Findメソッドで設定した検索条件で連続して検索を行います。

引数「After」で設定したセルの次のセルから検索を行ってくれて、検索内容の含まれるセルをRangeオブジェクトで返してくれます。

下の図は、クラス生徒の成績「英語」「数学」「国語」の3教科のそれぞれの点数と3教科合計点数を表にして、合計点の評価別に名前をリストアップできるようにします。

findnext

いつものようにユーザーフォームに配置したコマンドボタンを押すと指定したワークシートの表の中からリストアップして書き出すようにしてありますが、分かりやすくするために、【A】評価のみリストアップする方法を書いてみました。

Private Sub CommandButton1_Click()
Dim myRange As Range, meRange As Range, myAddress As String, i As Integer
Set meRange = Range(“J3:J22”)
Set myRange = meRange.Find(What:=Sheets(“test”).Range(“L2”).Value, LookIn:=xlValues)
If Not myRange Is Nothing Then
myAddress = myRange.Address
i = 3
Do
Cells(i, “L”).Value = myRange.Offset(, -8).Value
Set myRange = meRange.FindNext(After:=myRange)
i= i + 1
Loop Until myRange.Address = myAddress
Else
MsgBox”該当者がいません”
End If
End Sub

  1. コマンドボタン(CommandButton1)をクリックすると
  2. 変数myRangeとmeRangeと文字列型の変数myAddressと整数型の変数iを宣言する
  3. 変数meRangeに評価を検索する範囲のセルJ3~J22を指定する
  4. 指定したセル検索範囲変数meRangeに検索条件であるシート名【test】のセルL2と同じ値のセルを検索して、最初に見つけたセルを変数myRangeに保管する
  5. 変数myRangeがNothingでない場合(見つけたら)Ifステートメントを開始する
  6. 変数myAddressに見つけたセルを補完する
  7. 変数iには値を転記したい最初の行が3行目なので、i=3にします
  8. 以下の処理を繰り返す(Doステートメントの開始)
  9. i行目のL列(i,”L”)の値は見つけたセルの8列左の値(名前があるセル)を転記する
  10. 同じ条件で変数myRangeの次のセルから検索を開始して見つけたら変数myRangeに保管する
  11. 変数iに1を加える(1行下になります)
  12. 最初に見つけたセルと同じ値のセルを見つけるまで処理を繰り返す(探し続ける)
  13. 見つからなかった場合(Else)
  14. メッセージを表示する「該当者がいません」
  15. Ifステートメント終了宣言
  16. マクロ終了

これで合計点の評価がA評価の名前が検索できました。

これはあくまでも基本的な使い方なので、実際にはもう少し付け加えて、表の中から同じ検索条件で条件に合うデータだけを順に取り出しています。

例えば、上の図のように「A評価」だけではなく「B評価」「C評価」も全て一括で検索して表示する場合でも使っていますし、各科目別に「A評価」だけ取り出してみたりして使っています。

全ての評価を検索条件として評価別に転記する

先ほどのA評価のみ表示されている図を参考にすると、残りのB評価とC評価も一緒に表示させることができます。

もちろん同じコマンドボタンを1回だけクリックするだけで!!

簡単に言ってしまうと、15行目のEnd Ifと16行目のEnd Subの間にもう1回2行目~14行目を追加するという事です。

追加1回目がB評価、さらに追加2回目がC評価って事ですね。

そんなに難しくないでしょう?

ただし、同じ名前の変数が使えないので名前をちょっと変えれば大丈夫です。

下のコードは2回目のB評価を追加してみました。赤い文字のところです

Private Sub CommandButton1_Click()
Dim myRange As Range, meRange As Range, myAddress As String, i As Integer
Set meRange = Range(“J3:J22”)
Set myRange = meRange.Find(What:=Sheets(“test”).Range(“L2”).Value, LookIn:=xlValues)
If Not myRange Is Nothing Then
myAddress = myRange.Address
i = 3
Do
Cells(i, “L”).Value = myRange.Offset(, -8).Value
Set myRange = meRange.FindNext(After:=myRange)
i= i + 1
Loop Until myRange.Address = myAddress
Else
MsgBox”該当者がいません”
End If
Dim myyRange As Range, meeRange As Range, myyAddress As String, j As Integer
Set meeRange = Range(“J3:J22”)
Set myyRange = meeRange.Find(What:=Sheets(“test”).Range(“M2”).Value, LookIn:=xlValues)
If Not myyRange Is Nothing Then
myyAddress = myyRange.Address
j = 3
Do
Cells(j, “M”).Value = myyRange.Offset(, -8).Value
Set myyRange = meeRange.FindNext(After:=myyRange)
j= j + 1
Loop Until myyRange.Address = myyAddress
Else
MsgBox”該当者がいません”
End If
End Sub

追加して変数名を変えないとエラーになりますよ!変更したのは・・・

  1. myRangeをmyyRange
  2. meRangeをmeeRange
  3. myAddressをmyyAddress
  4. iをj

4つの変数を変えました!適当でしょう。でもこれで大丈夫なのです。

あとは、見つけたい評価をB評価に(L2をM2)に変えるだけです。

3つ目のC評価も今と同じように変数を適当に変更すれば大丈夫ですよ!

同じ条件で探し続けなくていいから特定の値だけ探す、そうVLOOKUP関数を使わずExcel-VBAでできないかな?

使い方覚えると応用範囲が広がります。

お時間があれば、VLOOKUP関数の代わりにエクセルVBAでやる方法もご覧くださいませ。

各コントロールまとめて転記シリーズ

ユーザーフォームを使うと配置したコントロール(テキストボックスやラベルなど)とセルとの間で値のやり取りが多くなります。

よく使うのでまとめてみました。

値の場所転記したい場所説明ページ
セルの値ラベル名VBAコード
セルの値テキストボックスの値VBAコード
セルの値コマンドボタンの表示名VBAコード
セルの値セルの値VBAコード
テキストボックス入力値セルの値VBAコード
波乗りアヒル
波乗りアヒル

成績表作成には知っておきたいExcel-VBAもくじ

ボウリングハンデキャップのつけ方
HDCPの種類はリーグのルールにより複雑に ボウリングの成績表(リーグ戦)で必ず必要になるのが、ハンデキャップ(HDCP)です。 ボウリングご予約の時に一番多く使われているのが、女性にだけハンデキャップをつけてあげることが多いですね。 だいだい何点ぐらいのハンデキャップが多いの?
ボウリングリーグ対戦表の数字を名前に自動変換する方法
リーグ戦の対戦表をエクセルで作る時、数字で表されている番号を名前に自動的に変換できると作業時間が短くなり効率的ですね。たった2つのエクセル関数を使うだけで、簡単にできるんです。今回の回戦数を入力するだけで一発変換完了。もちろんお客様にスケジュール対戦表として渡すことも簡単ですよ。
人数に応じてリーグ対戦表を自動選択する方法
複数の表があり、条件に合った表を選んで、さらにセルの値を選んで数字を名前に自動変換できると、とても便利なエクセルができますよね。ボウリングのリーグ対戦表も人数により表が異なるので、参加人数が決まったら、自動的に表を選んで対戦番号がこれも自動的に参加者名に変換されると便利で作業効率がアップして時間短縮になりますよ。
波乗りアヒル
波乗りアヒル

エクセルの勉強お疲れ様です。ちょっとひと休みしませんか?