Excel VBAのFindで完全一致検索にする方法｜2桁・3桁の番号が4桁や5桁に誤ヒットする原因と修正例

Visual Basic

Excel VBAで番号検索を作っていると、2桁や3桁の番号を検索した際に、その数字を含む4桁・5桁の番号へ飛んでしまうことがあります。たとえば「123」を検索したのに「51234」や「12345」に一致してしまうような状態です。

この原因は、VBAのFindメソッドで部分一致を意味する「LookAt:=xlPart」が指定されているためです。完全に同じ値だけを検索したい場合は、これを「LookAt:=xlWhole」へ変更します。

この記事では、Excel VBAのFindで完全一致検索にする方法、該当コードの修正箇所、数字が文字列として保存されている場合の注意点まで、実例を交えて解説します。

原因は「LookAt:=xlPart」が部分一致検索になっていること

今回のコードで最も重要なのは、Findメソッドの次の部分です。

Set r = .Find(What:=mystr(i), LookIn:=xlValues, LookAt:=xlPart)

この「LookAt:=xlPart」は、セル内容の一部に検索文字列が含まれていれば一致と判定する指定です。

たとえば検索値が「123」の場合、セルに「123」「1234」「5123」「991230」のいずれが入っていても一致候補になります。

完全一致にしたいなら「xlPart」を「xlWhole」に変える

完全に同じ番号だけへ移動したい場合は、修正するのは基本的に1か所です。

次のように変更します。

Set r = .Find(What:=mystr(i), LookIn:=xlValues, LookAt:=xlWhole)

「xlPart＝部分一致」「xlWhole＝完全一致」と覚えると分かりやすいです。

この修正により、検索値が「123」のときは「123」にだけ一致し、「1234」や「5123」には一致しなくなります。

修正後のVBA全文

元の処理をほぼそのまま残し、完全一致検索へ変更したコードは次のようになります。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim mystr As Variant
Dim i As Integer
Dim r As Range

If Target.Count <> 1 Then Exit Sub
If Target.Address <> "$B$1" Then Exit Sub

If Target.Value = "" Then
    Target.Select
    MsgBox "検索条件を入力してください"
    Exit Sub
End If

Set rng = Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row)
mystr = Split(Replace(Target.Value, " ", " "), " ")

For i = 0 To UBound(mystr)
    With rng
        Set r = .Find(What:=mystr(i), LookIn:=xlValues, LookAt:=xlWhole)
        If Not r Is Nothing Then
            Range("B" & r.Row).Select
            Exit Sub
        End If
    End With
Next i

Target.Select
MsgBox "検索条件に一致するものはありません"
End Sub

変更点は「LookAt:=xlPart」から「LookAt:=xlWhole」へ変えた部分です。それ以外の処理は元のままでも動作します。

xlPartとxlWholeの違いを具体例で比較

たとえばB列に次の番号が入っているとします。

セル 値
B2 12
B3 123
B4 1234
B5 5123

B1に「123」と入力した場合、LookAt:=xlPartではB3だけでなくB4やB5も検索対象になり得ます。

一方、LookAt:=xlWholeなら、セル全体が「123」と一致するB3だけが対象になります。

2桁・3桁・4桁・5桁が混在していても問題ない

完全一致検索に変更すれば、番号の桁数が混在していても問題ありません。

たとえば「12」「123」「1234」「12345」が同じ列に並んでいても、それぞれ入力した番号と完全に同じセルだけへ移動できます。

そのため、今回のように今後2桁・3桁の番号が追加される場合でも、検索処理自体を大きく作り直す必要はありません。

ただし数字と文字列が混在している場合は注意

Excelでは、見た目が同じ「123」でも、セル内部では数値の123として保存されている場合と、文字列の「123」として保存されている場合があります。

通常はFindで問題なく検索できますが、先頭ゼロを含む番号では注意が必要です。たとえば「0012」は数値として入力すると「12」になり、文字列として入力すると「0012」のまま保持されます。

管理番号として桁数や先頭ゼロが重要な場合は、B列を文字列形式に統一しておくとトラブルを避けやすくなります。

先頭ゼロを使う管理番号は「文字列」で統一する

たとえば番号に「0012」「0123」のような形式がある場合、セルの表示形式を「文字列」にしてから入力する方法があります。

あるいは先頭にアポストロフィを付けて、次のように入力します。

'0012

画面上では「0012」と表示されますが、Excel内部では文字列として保存されます。

管理番号は計算する数値ではないことが多いため、文字列として統一するほうが扱いやすいケースが多いです。

Findは以前の検索設定を引き継ぐことがある

Excel VBAのFindメソッドには注意点があります。Findでは一部の検索条件が、Excel上で最後に使った検索設定を引き継ぐことがあります。

そのため、安定したVBAにするなら、LookInやLookAtだけでなく、SearchOrderやSearchDirectionなども明示しておくと安全です。

たとえば次のように書けます。

Set r = .Find(What:=mystr(i), _
              After:=.Cells(.Cells.Count), _
              LookIn:=xlValues, _
              LookAt:=xlWhole, _
              SearchOrder:=xlByRows, _
              SearchDirection:=xlNext, _
              MatchCase:=False)

こうしておけば、ユーザーが手動で検索ダイアログを使った後でも、VBA側の検索条件が影響を受けにくくなります。

今回の用途ならSplit処理は不要な可能性もある

元コードでは次の処理があります。

mystr = Split(Replace(Target.Value, " ", " "), " ")

これはB1に複数の検索語をスペース区切りで入力し、それぞれ順番に検索するための処理に見えます。

もしB1には必ず1つの番号しか入力しないのであれば、この処理は必須ではありません。よりシンプルなコードにできます。

1つの番号だけ検索するなら、さらに簡潔に書ける

B1には1つの番号しか入力しない前提なら、次のように書くこともできます。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim r As Range

If Target.Count <> 1 Then Exit Sub
If Target.Address <> "$B$1" Then Exit Sub

If Target.Value = "" Then
    MsgBox "検索条件を入力してください"
    Target.Select
    Exit Sub
End If

Set rng = Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)

Set r = rng.Find(What:=Target.Value, _
                 LookIn:=xlValues, _
                 LookAt:=xlWhole)

If Not r Is Nothing Then
    r.Select
Else
    Target.Select
    MsgBox "検索条件に一致するものはありません"
End If
End Sub

元コードより短いため、今後メンテナンスするときにも分かりやすくなります。

複数の同じ番号がある場合は最初の1件に移動する

Findは条件に一致するセルが複数ある場合、最初に見つかったセルを返します。

そのためB列に同じ管理番号が重複している場合は、そのうち1件だけへ移動します。

もし重複を許さない番号表であれば、入力時に重複チェックを入れるか、条件付き書式などで重複を見つけやすくしておくと管理しやすくなります。

完全一致でもスペースが入っていると一致しない

LookAt:=xlWholeにすると、余分なスペースも含めてセル全体が一致している必要があります。

例えばB列に「123 」と末尾スペース付きで保存されていて、B1に「123」と入力した場合は完全一致にならない可能性があります。

番号データに余分な空白が混ざっている場合は、データ側をTRIM関数などで整理するか、VBA側でTrimを使う方法があります。

例えば検索値側なら次のようにできます。

Set r = .Find(What:=Trim(mystr(i)), LookIn:=xlValues, LookAt:=xlWhole)

数値だけを扱うならValで変換する方法もある

番号がすべて純粋な数値で、先頭ゼロを使わないのであれば、検索値を数値へ変換して扱う方法もあります。

例えば次のようにします。

What:=Val(Target.Value)

ただし「0012」と「12」を別の番号として扱いたい場合にはValを使うべきではありません。両方とも12として扱われるためです。

管理番号では、数字に見えても文字列として扱うほうが安全な場合が多いです。

変更する場所は結局どこなのか

元のコードで最低限変更すべき場所は、次の1行だけです。

Set r = .Find(What:=mystr(i), LookIn:=xlValues, LookAt:=xlPart)

これを次のように変更します。

Set r = .Find(What:=mystr(i), LookIn:=xlValues, LookAt:=xlWhole)

「xlPart」を「xlWhole」にするだけで、部分一致から完全一致検索へ切り替わります。

修正前にはVBAファイルのバックアップを取る

VBAに慣れていない場合は、編集前にExcelファイルをコピーしてバックアップしておくと安心です。

特にマクロ有効ブックの場合は、拡張子が「.xlsm」などになっていることを確認してください。「.xlsx」で保存するとマクロが保存されません。

まずファイルを複製し、コピー側でコードを変更して正常に動作することを確認してから本番ファイルへ反映する方法が安全です。

まとめ｜「LookAt:=xlWhole」に変えれば完全一致検索になる

Excel VBAのFindで、2桁や3桁の番号を検索した際に4桁・5桁のセルへ飛んでしまう原因は、検索条件に「LookAt:=xlPart」が指定されていることです。

完全に同じ番号だけを検索したい場合は、「LookAt:=xlPart」を「LookAt:=xlWhole」へ変更します。

これにより「123」を検索したときに「1234」や「5123」へ誤って移動することはなくなり、「123」と完全一致するセルだけが対象になります。さらに、先頭ゼロを使う管理番号では文字列形式へ統一し、余分なスペースや重複データにも注意すると、より安定した検索機能として運用できます。

コメント

タイトルとURLをコピーしました