エクセルVBA Findメソッドでの検索の方法とコード組み立てのコツ

vbafindeyecatch001

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

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

 

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

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

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

 

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

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

 

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

 

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(完全一致)を指定します。

コード
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(完全一致)を指定します。

コード
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

 

 

MEMO

最終行を取得するほうほうについての記事はこちらです。

エクセルVBA データ入力済セルの最終行番号を取得する方法

Offsetの使い方についてはこちらの記事を参考にしてください。

エクセルVBA Offsetの使い方。セルや選択範囲を移動する

Do Loopの使い方についてはこちらの記事が参考になります。

VBA 回数不定のループ処理はDo LoopとFor Each

 

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

 

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

一部の語句を含むコード

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

コード
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メソッドでの検索の方法のまとめ

 

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

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

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

 

備忘のポイント

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

 

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

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

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

 

検索系の関数を、特徴を押さえながら学習するには

 

特に検索系の関数などもそうですが、

似たものを用途によって使い分けていくことが必要になります。

「どういう風に覚えたらいいの?」

もちろんそれはお互いを対比しながら学習することが必要になります。

参考書をペラペラ繰りながらの学習です。

自分に合った参考書を見つけることは大切です。分かりづらい文章でページを繰っていても能率は期待できません。

 

短期間でエクセルVBAの独学習得を目指したいなら

 

エクセルVBAを独学する独習方法は、学習者それぞれ十人十色、多種多様と思われます。

けれども、

出来るだけ効率よく学習するためには、いくつかの大切なポイントがあります。

独学でもVBA習得の中級クラスに達するのはそんなに難しいことではありません。

先人が行った勉強方法をあなたがそのまま利用すればよいということです。

vbastudyeyecatch002 エクセルVBAを独学で習得する!ために大切な7つのポイントを解説します

独習のための大切な7つのポイントは、上記記事にて解説しています。

重要ワード

独習によるVBA習得のキーワードは、

出来るだけ多くの実例に触れること!

です。

正直、VBAの学習について自分の周りの仕事(業務)からだけ実例を得るのでは効率良い習熟は無理です。

ハッキリ言って、

本当に短い期間でVBA習得を成功させたいなら、今使っている参考書が良書かどうかを判断し、新ツールとしてオンライン学習も取り入れて行うことが、

手っ取り早く短期間習得できるというのは間違いないでしょう。

 

このサイトでよく使うVBAのコードのトピックを纏めています。

「VBA最速理解」の記事一覧を開く

 

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

 

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