VBA Findでセルを検索する方法(完全一致・部分一致) Excel ホームページ制作 | 墨田区

VBA Findでセルを検索する方法(完全一致・部分一致) Excel

LINEで送る
Pocket

Excel VBAで指定したセル範囲の中から値を検索する方法のご紹介です。
VBA Findでセルを検索する方法(完全一致・部分一致) Excel

検索にあたっては完全一致で探したい場合と、前方一致や後方一致などの部分一致で検索したい場合がありますね。セルの検索には Find メソッドを使い、見つかったセルの次のセルを検索するには、FindNext メソッドと、FindPrevious メソッドを使用します。

Find メソッドの引数はたくさんありますが、「Ctrl + F」で出てくる「検索と置換」ダイアログと同じだと思えばイメージしやすいのではないでしょうか。
VBA Findメソッドは検索と置換ダイアログと同じ

ここでは Find メソッドを使って、セルを検索する方法 をご紹介します。




【PR】マジか?!「アレ」してるLINEスタンプっていったい・・・


Findの構文と引数

まずは Find の構文と引数を理解しましょう。引数が多いですが、省略も可能です。
expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte)
expression.FindNext(After)
expression.FindPrevious(After)

引数省略説明
expression Range オブジェクト。
What×検索対象のデータを指定。
Afterここで指定したセルの次のセルから検索が開始される。この引数を省略すると、セル範囲の左上端のセルから検索が開始される。
LookIn検索の対象を指定。xlFormulas(数式)、xlValues(値)、xlComments(コメント)のいずれかを指定。
LookAtxlWhole(完全一致)、xlPart(部分一致) のいずれかを指定。
SearchOrder検索方向を指定。xlByColumns(列方向に検索)、xlByRows(行方向に検索)
SearchDirectionxlNext(前方に検索)、xlPrevious(後方に検索)
MatchCase大文字と小文字の区別。True(区別する)、False(区別しない)
MatchByte半角と全角を区別。True(区別する)、False(区別しない)
SearchFormat書式を検索。True(検索する)、False(検索しない)

条件に当てはまるセルが存在しない場合には、Nothing が返されます。省略できる引数が多いですが、トラブルを防ぐためにもできるだけ省略せず、明示的に記述することをおすすめします。

Findメソッドで部分一致をおこなう

それでは、Find メソッドで部分一致をおこなうサンプルを作ってみましょう。

検索するシートに、データベースを表す「DB」ってシート名をつけます。A列には連番、B列には日本の多い名字ベスト20から抜粋した馴染みのある名前です。空行なしで設定されていることが前提です。
日本の多い名字ベスト20DBシート

この名字の中から特定の文字を持つ人だけを抽出するサンプルを作るとすれば、下記のようになるでしょうか。
'
' 苗字の部分一致検索
' 引数      :Value 検索文字列
' 戻り値    :なし
'
Public Sub FindLastNameBroadMatch(Value As String)
  Dim DbSheet As Worksheet
  Dim DbRng As Range
  Dim FoundCell As Object
  Dim FirstFound As Object
  Dim StartRng As Range

  ' ワークシートを指定します。
  Set DbSheet = Worksheets("DB")
  ' 検索範囲を指定します。
  Set DbRng = DbSheet.Range("B:B")
  ' スタート位置を指定します。
  Set StartRng = DbSheet.Range("B" + CStr(DbSheet.Range("B1").End(xlDown).Row))
  ' 指定された文字列で部分一致検索します。
  Set FoundCell = DbRng.Find(Value, StartRng, , xlPart, , , True, True)
  ' 見つかった場合
  If Not FoundCell Is Nothing Then
    ' 最初に見つかったセルを保存します。
    Set FirstFound = FoundCell
    Do
      ' セル値をイミディエイトウィンドウ(Ctrl + G)に出力します。
      Debug.Print FoundCell.Value
      ' 指定された文字列が出現する次のセルを検索します。
      Set FoundCell = DbRng.FindNext(FoundCell)
    ' 最初に指定された文字列が見つかったセルに戻るまでループします。
    Loop Until FoundCell.Address = FirstFound.Address
  End If
End Sub

ここから”山”って漢字を持つ名字と、”佐”って漢字を持つ名字を検索してみましょう。

イミディエイトウィンドウ(Ctrl + G)に下記を入力してみてください。
FindLastNameBroadMatch "山"
FindLastNameBroadMatch "佐"

結果はこうなります。
FindLastNameBroadMatch "山"
山田
山口
山本
FindLastNameBroadMatch "佐"
佐藤
佐々木

ポイントは、検索のスタートセルを決めている箇所になりますかね。

Find メソッドの第2引数でスタート位置を決めないと、”佐”で検索した時に、佐々木⇒佐藤の順番で表示されてしまいます。先頭のセル(B1)から検索するには、引数Afterに検索範囲の最後のセルを指定しなければなりません。そのため Range(“B1”).End(xlDown).Row で最終行を取得して、列である”B”と結合して Range を作っています。
※Range(“B1”).End(xlDown).Row は、Ctrl + ↓(PgDn) と同じ動作です。

Find メソッドの引数は、「検索と置換」のダイアログボックスへも引き継がれます。What:=”山”、MatchCase:=True、MatchByte:=Trueを設定した場合、下記のように「検索する文字列」に”山”、「大文字と小文字を区別する」にチェック、「半角と全角を区別する」にチェックが入りました。
VBA Find メソッドの引数は検索と置換ダイアログへ引き継がれる

Find メソッド引数を省略した場合には、前回の値を使用して処理します。つまり、マクロ実行後に「検索と置換」のダイアログボックスで検索条件を設定した後、更にマクロを実行すると、検索条件を引き継いでしまうことになります。

正しい検索結果を得るためには、できるだけ明示的に記述しましょう。

Findメソッドで完全一致をおこなう

今度は Find メソッドで完全一致をおこなうサンプルを作ってみましょう。

下図のように、”DB”シートに「部署」を表す項目を追加します。
VBA Findメソッド DBシートに部署項目追加

ここから特定の部署の人だけを探して、名前を表示するサンプルを書くとすると、下記のような感じでしょうか。
'
' 部署の完全一致検索
' 引数      :Value 検索文字列
' 戻り値    :なし
'
Public Sub FindDepartmentNameExactMatch(Value As String)
  Dim DbSheet As Worksheet
  Dim DbRng As Range
  Dim FoundCell As Object
  Dim FirstFound As Object
  Dim StartRng As Range

  ' ワークシートを指定します。
  Set DbSheet = Worksheets("DB")
  ' 検索範囲を指定します。
  Set DbRng = DbSheet.Range("C:C")
  ' スタート位置を指定します。
  Set StartRng = DbSheet.Range("C" + CStr(DbSheet.Range("C1").End(xlDown).Row))
  ' 指定された文字列で完全一致検索します。
  Set FoundCell = DbRng.Find(Value, StartRng, , xlWhole)
  ' 見つかった場合
  If Not FoundCell Is Nothing Then
    ' 最初に見つかったセルを保存します。
    Set FirstFound = FoundCell
    Do
      ' 見つかったセルの行を基に、DBシートから名字を取得し、イミディエイトウィンドウ(Ctrl + G)に出力します。
      Debug.Print DbSheet.Cells(FoundCell.Row, 2).Value
      ' 指定された文字列が出現する次のセルを検索します。
      Set FoundCell = DbRng.FindNext(FoundCell)
    ' 最初に指定された文字列が見つかったセルに戻るまでループします。
    Loop Until FoundCell.Address = FirstFound.Address
  End If
End Sub

ここから”営業部”の人だけを検索してみましょう。

イミディエイトウィンドウ(Ctrl + G)に下記を入力してみてください。
FindDepartmentNameExactMatch "営業部"

結果はこうなります。
FindDepartmentNameExactMatch "営業部"
佐藤
山田
佐々木
木村
山本

ふむふむ、いい感じですね^^

参考サイト

Find メソッド

まとめ

データを探す・・という行為は、実務に限らず私たちの生活でも日常的なことになりました。
効率よく精度の高いデータを抽出するために、Find メソッドを上手に使いたいところですね。

今回のサンプルでは Find メソッドの引数をかなり省略しています。でもホントは明示的に記述しなければなりません。特に Excel では「検索・置換」ダイアログとも連動してしまうので、明示的に記述しないと動作保障できなくなってしまいます。

以前、私の作ったマクロで不具合がありました。それは、大文字・小文字の判断を省略していたことです。例えば、”ABC”と”abc”は違うキーワードなのに同じと判断され、予想と違う結果になってしまったのです。途中で実務者が気が付いたので大事には至りませんでしたが、何かあってからでは大変なことになるところでした。

不具合はいつでも人が引き起こします。皆さんも気を付けて使ってくださいね。

おつかれさまでした。

LINEで送る
Pocket

この記事がお役に立ちましたら シェア をお願いいたします。

コメントを残す

コメント(必須)

お名前 (必須)
メールアドレス
(アドレスは公開されません)

Trackback URL