Findメソッドでの検索の方法とコード組み立てのコツ

vbafindeyecatch001

エクセルの検索ツールと同機能のものが、エクセルVBAではFindメソッドで行うことが出来ます。

この記事ではそのコードのセットの方法とコード組み立てのコツを紹介します。

こんにちは、じゅんぱ店長(@junpa33)です。

今回は、データの検索方法で、エクセルVBAでできるメソッドについてです。

「FindとFindNextメソッド」の使い方と最速理解のコードの組み立て方を説明したいと思います。

値の検索には、他にも動作のよく似た関数があります。それぞれに固有の特徴がありますが、特に、

関数が返してくる値に注目してください。

  • Match

検索対象の値が、指定した範囲内でどの位置にあるかを相対的に位置数値(行または列番号)として返します。

vbamatcheyecatch001 VBAで使うMatch関数 活用度アップでテッパン関数に!
  • Index

テーブルまたはセル範囲にある値、またはその値のセル参照を返します。

vbaindexeyecatch001 VBA Index関数 の使い方。行と列の指定でセル値の抽出
  • Vlookup

選択範囲の1列目で指定した値を検索し、その選択範囲内で取出指定した別の列の同じ行にある値を返します。

関数VLookupをエクセルVBAで使う。利用法と注意点
  • Find・FindNext

ワークシートで特定の情報をRangeオブジェクトを対象として検索しセルを表すRangeオブジェクトとして返します。

Findメソッドでの検索の方法とコード組み立てのコツ

vbafindsetumeip005

Findメソッドはエクセルの既定ツールの「検索」と同じです。

Findメソッドは、指定されたセル範囲内を対象として検索を行います。

ただし対象先はRangeオブジェクトになりますので、いきなり値などを表示するのではありません。

そのオブジェクトに、必要なプロパティを繋げることで、そのセルの値や位置などセルの情報を取得・設定できるようになります。

Findメソッドの構文は

構文(記述方法)は

Object. Find ( What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat )

です。

What(検索値)必須

検索するデータを指定します。

After(検索開始位置)省略可能

ここに指定したセルの次から検索を開始します。省略すると指定範囲の左上セルになります

セルをRangeオブジェクトとして指定します。

LookIn(検索対象種類)省略可能

検索する対象を指定します。

xlFormulas(数式)、xlValues(値)、xlComents(セルのコメント)の3種類

既定値はxlFormulas(数式)です。

LookAt(一致区分)省略可能

完全一致検索をするかどうか指定します

xlPart(部分一致)、xlWhole(完全一致)の2種類

既定値はxlPart(部分一致)です。

SearchOrder(検索方向)省略可能

検索の方向を指定します。

xlByRows(行方向)、xlByColumns(列方向)の2種類

既定値はxlByRows(行方向)です。

SearchDirection(検索順序)省略可能

検索の進行方向を指定します。

xlNext(開始セルより後方を検索)、xlPrevious(開始セルより前方を検索)

既定値はxlNext(開始セルより後方を検索)です。

MatchCase(大小文字区別)省略可能

大文字と小文字を区別して検索するかどうかを指定します。

TrueかFalseです。

既定値はFalseです。

MatchByte(全半角l区別)省略可能

半角文字と全角文字を区別して検索するかどうかを指定します。

TrueかFalseです。

既定値はFalseです。

SearchFormat(書式検索)省略可能

書式を検索の条件に含めるかどうかを指定します。

TrueかFalseです。

既定値はFalseです。

今回の例題

今回も雑誌リストを例題とします。

をVBAコード化します。

vbafindsetumei001

1.雑誌名の完全一致のコード

Findで検索抽出したRangeオブジェクトはオブジェクト変数に代入することになりますが、

その時は必ず「Setステートメント」を使います。

雑誌名の完全一致コード

検索値が検索範囲の中で一致しなかったときは「Nothing」が返されます。

今回は、その「Nothing」かどうかを調査せず、エラーを無視するコード設計にしています。

引数LookAtでxlWhole(完全一致)を指定します。

VBA
Option Explicit

Sub Findメソッド1()
    Dim DataRange As Range
    Dim PicRange As Range
    Dim Arow As Long, sKa As Variant
'Arowはデータ表の行数を表します。
        Arow = Cells(6, 2).CurrentRegion.Rows.Count
'データレンジを雑誌名列にセットします。
        Set DataRange = Range(Range("G6"), Range("G" & Arow + 5))
'検索値の入力
        sKa = Range("L1").Value
'エラーストップを無視するコード
        On Error Resume Next
'データレンジから検索値を調べて、PicRangeにセットします。
        Set PicRange = DataRange.Find(sKa, LookIn:=xlValues, _
            LookAt:=xlWhole)
        Range("L2") = Cells(PicRange.Row, 9).Value
        Range("L3") = Cells(PicRange.Row, 6).Value
'エラー無視を解除する
        On Error GoTo 0
End Sub

実行結果

検索値に「MonoMax」と指定し「1390」と「宝島社」が正常に抽出されました。

vbafindsetumei002

2.出版社名の完全一致すべての雑誌名

「FindNext」を使って再検索を行います。

構文は、オブジェクト(検索範囲). FindNext ( Findで指定したオブジェクト変数 ) です。

Findの時と同じで、必ず「Setステートメント」を使いオブジェクト変数に代入します。

Findで指定したオブジェクト変数にFindNextで見つけたRangeオブジェクトを代入して上書きするイメージです。

出版社名の完全一致のコード

「Do~Loop」で再検索を実行します。

抜け出す条件の設定は、再検索したセルのアドレスが最初に検索したセルのアドレスと一致したら検索終了とみなします。

引数LookAtでxlWhole(完全一致)を指定します。

VBA
Sub Findメソッド2()
    Dim DataRange As Range
    Dim PicRange As Range
    Dim PicAdd As String
    Dim RePRange As Range
    Dim Arow As Long, sKb As Variant
    Dim n As Long
'Arowはデータ表の行数を表します。
        Arow = Cells(6, 2).CurrentRegion.Rows.Count
'データレンジを雑誌名列にセットします。
        Set DataRange = Range(Range("F6"), Range("F" & Arow + 5))
'検索値の入力
        sKb = Range("K31").Value
'データレンジから検索値を調べて、PicRangeにセットします。
        Set PicRange = DataRange.Find(sKb, LookIn:=xlValues, _
            LookAt:=xlWhole)
'Offsetの移動値の初期設定
        n = 0
'検索値に合致する値が無いこととが無い(ある)時
        If Not PicRange Is Nothing Then
'見つかったセルのアドレスを「PicAdd」に格納する
            PicAdd = PicRange.Address
'次の検索を行うため作業をループする
            Do
'見つかったセルから必要データのあるセルに移動しその値を抽出する
                Range("L31").Offset(n) = Cells(PicRange.Row, 7).Value
                Range("M31").Offset(n) = Cells(PicRange.Row, 9).Value
'FindNextメソッドで次を検索する
                Set PicRange = DataRange.FindNext(PicRange)
'新たに見つかったセルのアドレスが、最初のセルのアドレスに一致した場合
                If PicRange.Address = PicAdd Then Exit Do
'Offsetの移動値を1つ増やす
                n = n + 1
            Loop
        End If
End Sub

実行結果

検索値を「小学館」と入力した結果3冊の雑誌が抽出されました。

vbafindsetumei003

一部の語句を含むジャンルの雑誌名

「出版社名の完全一致」の場合とほぼ同じコードになっています。

一部の語句を含むコード

引数LookAtでxlPart(部分一致)を指定します。

VBA
Sub Findメソッド3()
    Dim DataRange As Range
    Dim PicRange As Range
    Dim PicAdd As String
    Dim RePRange As Range
    Dim Arow As Long, sKc As Variant
    Dim n As Long
'Arowはデータ表の行数を表します。
        Arow = Cells(6, 2).CurrentRegion.Rows.Count
'データレンジを雑誌名列にセットします。
        Set DataRange = Range(Range("D6"), Range("D" & Arow + 5))
'検索値の入力
        sKc = Range("K31").Value
'データレンジから検索値を調べて、PicRangeにセットします。
        Set PicRange = DataRange.Find(sKc, LookIn:=xlValues, _
            LookAt:=xlPart)
'Offsetの移動値の初期設定
        n = 0
'検索値に合致する値が無いこととが無い(ある)時
        If Not PicRange Is Nothing Then
'見つかったセルのアドレスを「PicAdd」に格納する
            PicAdd = PicRange.Address
'次の検索を行うため作業をループする
            Do
'見つかったセルから必要データのあるセルに移動しその値を抽出する
                Range("L31").Offset(n) = Cells(PicRange.Row, 7).Value
                Range("M31").Offset(n) = Cells(PicRange.Row, 9).Value
'FindNextメソッドで次を検索する
                Set PicRange = DataRange.FindNext(PicRange)
'新たに見つかったセルのアドレスが、最初のセルのアドレスに一致した場合
                If PicRange.Address = PicAdd Then Exit Do
'Offsetの移動値を1つ増やす
                n = n + 1
            Loop
        End If
End Sub

実行結果

「男」を含むジャンルの雑誌を全て抽出しました。

vbafindsetumei004

Findメソッドでの検索の方法のまとめ

vbafindsetumeip006

Findメソッドの引数はエクセルシートでの「検索」と同じです。

引数をいろいろ設定すれば細かく検索することも出来ます。

このFindメソッドを使うための備忘のポイントは

備忘のポイント
  • 戻り値がRangeオブジェクト。なので、「Setステートメント」を使ってオブジェクト変数に検索セルを代入すること。
  • LookAt(一致区分)でxlPart(部分一致)、xlWhole(完全一致)を切り替えるだけでワイルドカード的に検索できる。
  • 検索結果無しの場合は「Nothing」が返る。処理方法をセットしないとエラーストップする。
  • FindNextを使うときはDo~Loopの中に入れ込むが、終了条件には、検索セルのアドレス比較を使うと簡単に処理できる

今回説明してきました「値」に関しての設定であれば、大丈夫ですが、

検索条件に「書式」を含めて行う場合は、その検索終了後も検索書式の設定が引き継がれてしまいます。

書式検索のコード組み立て手順も今回とは異なりますので、こちらはまた別機会に紹介させていただきます。

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。

vbastudyeyecatch2 エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です

今回の記事はここまでです。   最後までご覧いただき有難うございました。

エクセルVBA最速理解で必要な知識を集めよう!

エクセルVBA業務ツールで日常の業務改善を行いましょう。

VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません。