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

まずは Find の構文と引数を理解しましょう。引数が多いですが、省略も可能です。

expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte)
expression.FindNext(After)
expression.FindPrevious(After)

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

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

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

この名字の中から特定の文字を持つ人だけを抽出するサンプルを作るとすれば、下記のようになるでしょうか。

'
' 苗字の部分一致検索
' 引数      ：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 メソッドの第２引数でスタート位置を決めないと、"佐"で検索した時に、佐々木⇒佐藤の順番で表示されてしまいます。先頭のセル(B1)から検索するには、引数Afterに検索範囲の最後のセルを指定しなければなりません。そのため Range("B1").End(xlDown).Row で最終行を取得して、列である"B"と結合して Range を作っています。

Range("B1").End(xlDown).Row は、Ctrl + ↓(PgDn) と同じ動作です。

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

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

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

今度は 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 メソッド

Excel VBAで指定したセル範囲の中から値を検索する方法を紹介しました。

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

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

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

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