VBAのFindメソッドの使い方には注意が必要です

公開日：2013-06-06　最終更新日：2021-08-22

VBAのFindメソッドの使い方には注意が必要です

VBA Find メソッド

vba find での検索が極めて多く、Findメソッドは検索からの流入ではトップクラスです、
アクセス解析で分かった事ですが正直少し戸惑っています。
なぜなら私はFindメソッドをほとんど使いません、
Match関数や配列を使って処理したほうが高速かつ確実に動作するからです。

確かに、Findメソッドについての掲載はあります、こちらです。
マクロVBA入門.第98回.Findメソッド,FindNextメソッド（検索,次を検索）

・Findメソッド・FindNext メソッド・FindPrevioust メソッド・Application.FindFormatメソッド・FindメソッドとFindNextメソッドの実戦例

ですが、マクロ入門として解説しているにすぎません。
他のサンプルコードでは、ほとんど使っていません。

なぜ、私がFindメソッドを使わないかと言うと、理由は以下になります。

1.処理速度が遅い
2.指定オプションがシート操作とリンクしている
3.「値」で検索した場合は、表示形式に依存した検索になる

簡単に言うと、この3つになります。

一番の理由は、1.処理速度が遅い、これに尽きます。

2.は、並べ替え(Sort)でも同じ事ですが、
Sortは高速で処理されますし、何より有効な代替え手段がありません。

3.については、LookIn:とLookAtが関係します。
この仕様を理解するのはかなり大変です。
事前にわかっていれば、しっかりオプションを設定したり、マクロVBAで表示形式を整える等の対策ができますがかなり面倒です。、

検索については他の（Match関数や配列を使う）方法があり、むしろその方が処理速度も速く動作が確実です。

1.処理速度が遅い

以下の２つのプロシージャーでテストします。

※実行時間については、計測環境に大きく左右されますので、あくまで参考数値としてお考えください。


  
    
      Sub test1()

      　　Debug.Print Timer

      　　Dim i As Long

      　　Dim rng As Range

      　　For i = 1 To 1000

      　　　　Set rng = Range("A:A").Find(What:="a10000", _

      　　　　　　　　　　　　　　　　　　LookIn:=xlValues, _

      　　　　　　　　　　　　　　　　　　lookat:=xlWhole, _

      　　　　　　　　　　　　　　　　　　SearchOrder:=xlByColumns, _

      　　　　　　　　　　　　　　　　　　MatchByte:=False)

      　　Next

      　　Debug.Print Timer

      End Sub


  
    
      Sub test2()

      　　Debug.Print Timer

      　　Dim i As Long

      　　Dim rng As Range

      　　For i = 1 To 1000

      　　　　Set rng = Cells(WorksheetFunction.Match("a10000", Range("A:A"),
      0), 1)

      　　Next

      　　Debug.Print Timer

      End Sub

※どちらもエラー処理は入れていません、単純な処理速度比較の為です。

A列のA1～A10000に、a1,a2,・・・a10000
このようにデータを入れて、上記をテスト実行すると、

test1 : 6.78秒
test2 : 0.42秒

これ程までに差が出ます。
これでも、Findメソッドを使う理由があるでしょうか。
恐らく、市販の書籍やVBA解説サイトで常に高い優先順位で解説しているのかもしれません。

当サイトでは、Findメソッドより先に、WorksheetFunctionを解説しています。
第87回.WorksheetFunction（ワークシート関数を使う）

・ワークシート関数の使い方・WorksheetFunctionで使用できる関数・個別の関数の使い方・関数の結果（戻り値）・WorksheetFunctionの使用例．・検索系の関数での日付の扱い・WorksheetFunctionのエラー対処・最後に

優先順位は間違いなく、WorksheetFunctionが先であり、
WorksheetFunctionで処理可能なら、これを使うべきだと考えています。

ここで、
FindNextメソッドを使うような、繰り返し処理はどうするのか・・・
こんな声が聞こえてきそうです。

こういう場合には、まずは配列を使います。
以下の２つのプロシージャーでテストします。


  
    
      Sub test3()

      　　Debug.Print Timer

      　　Dim i As Long

      　　Dim rng As Range

      　　Dim firstAddress As String

      　　For i = 1 To 100

      　　　　With Range("A:A")

      　　　　　　Set rng = .Find(What:="101", _

      　　　　　　　　　　　　　　LookIn:=xlValues, _

      　　　　　　　　　　　　　　lookat:=xlPart, _

      　　　　　　　　　　　　　　SearchOrder:=xlByColumns, _

      　　　　　　　　　　　　　　MatchByte:=False)

      　　　　　　If Not rng Is Nothing Then

      　　　　　　　　firstAddress = rng.Address

      　　　　　　　　Do

      　　　　　　　　　　rng.Interior.Color = vbRed

      　　　　　　　　　　Set rng = .FindNext(rng)

      　　　　　　　　　　If rng Is Nothing Then

      　　　　　　　　　　　　Exit Do

      　　　　　　　　　　End If

      　　　　　　　　　　If rng.Address = firstAddress Then

      　　　　　　　　　　　　Exit Do

      　　　　　　　　　　End If

      　　　　　　　　Loop

      　　　　　　End If

      　　　　End With

      　　Next

      　　Debug.Print Timer

      End Sub


  
    
      Sub test4()

      　　Debug.Print Timer

      　　Dim i As Long

      　　Dim rng As Range

      　　Dim ix As Long

      　　Dim ary As Variant

      　　Dim firstAddress As String

      　　For i = 1 To 100

      　　　　Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))

      　　　　ary = rng

      　　　　For ix = LBound(ary) To UBound(ary)

      　　　　　　If ary(ix, 1) Like "*101*" Then

      　　　　　　　　rng.Cells(ix, 1).Interior.Color = vbRed

      　　　　　　End If

      　　　　Next

      　　Next

      　　Debug.Print Timer

      End Sub

先ほどと同様に、A列のA1～A10000に、a1,a2,・・・a10000

このようにデータを入れて、上記をテスト実行すると、
test3 : 1.65秒
test4 : 0.86秒
結構な差が出ています。

FindNextメソッドを一生懸命覚えるなら、配列処理を覚えた方がよいですね。
そもそも、VBAの簡単な判断として、
Rangeオブジェクトのメソッドは遅いものだと認識しておいた方が良いでしょう。

参考までに
このような場合にもっと速くしたいのなら、Unionメソッドを使う方法があります。


  
    
      Sub test5()

      　　Debug.Print Timer

      　　Dim i As Long

      　　Dim rng As Range

      　　Dim rng2 As Range

      　　Dim ix As Long

      　　Dim ary As Variant

      　　Dim firstAddress As String

      　　For i = 1 To 100

      　　　　Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))

      　　　　ary = rng

      　　　　For ix = LBound(ary) To UBound(ary)

      　　　　　　If ary(ix, 1) Like "*101*" Then

      　　　　　　　　If rng2 Is Nothing Then

      　　　　　　　　　　Set rng2 = rng.Cells(ix, 1)

      　　　　　　　　Else

      　　　　　　　　　　Set rng2 = Union(rng2, rng.Cells(ix, 1))

      　　　　　　　　End If

      　　　　　　End If

      　　　　Next

      　　　　rng2.Interior.Color = vbRed

      　　Next

      　　Debug.Print Timer

      End Sub

上記と同様テストで、
test4 : 0.63秒
若干ではありますが、確実に速くはなります。

書き方は何通りもありますので、いろいろ工夫してみるのも良いでしょう。

2.指定オプションがシート操作とリンクしている

Findの構文

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

引数の説明

What	検索するデータです。文字列など、セル内のデータに該当する値を指定します。
After	セル範囲内のセルの 1 つを指定します。このセルの次のセルから検索が開始されます。引数 After で指定するセルは、コードからではなく、通常の画面上で検索を行う場合のアクティブセルに該当します。このセルの次から検索が開始されるため、範囲内の他のセルがすべて検索され、このセルに戻るまで、このセル自体は検索されません。この引数を省略すると、対象セル範囲の左上端のセルが検索の開始点になります。
LookIn	情報の種類を指定します。 xlFormulas：数式 xlValues：値 xlComents：コメント文
LookAt	xlPart：検索テキストの一部を検索します。 xlWhole：検索テキスト全体を検索します。
SearchOrder	xlByColumns：列を下方向に検索してから、次の列に移動します。 xlByRows：行を横方向に検索してから、次の行に移動します。
SearchDirection	xlNext：一致する次の値を検索します。 xlPrevious：一致する前の値を検索します。
MatchCase	大文字と小文字を区別するには、True を指定します。既定値は False です。
MatchByte	この引数は、2 バイト (全角) 文字の言語サポートが選択またはインストールされている場合にだけ使用できます。 2 バイト文字が 2 バイト文字とだけ一致するようにする場合は、True を指定します。 2 バイト文字が 2 バイト文字だけではなく、対応する 1 バイト文字とも一致するようにする場合は False を指定します。
SearchFormat	検索の書式を指定します。

引数LookIn、LookAt、SearchOrder、およびMatchByteの設定は、このメソッドを使用するたびに保存されます。
次にこのメソッドを使用するときにこれらの引数の指定を省略すると、保存された設定が使用されます。
これらの引数の設定を変更すると、[検索と置換]ダイアログボックスに表示される設定が変わります。
また、[検索と置換]ダイアログボックスで設定を変更すると、保存されている値、つまり引数を省略した場合に使用される値が変わります。
このような設定の変更によって生じる問題を避けるには、メソッドを使用するたびに、これらの引数を明示的に指定します。

この引数の問題により、
マクロを実行するユーザーに誤解を与える可能性があります。
シート操作で「検索」は頻繁に使われる機能なので、
Findメソッドを使うと、ユーザーが[検索と置換]を使うときに余分な負担をかけてしまう可能性があります

3.「値」で検索した場合は、表示形式に依存した検索になる

[検索と置換]ダイアログボックスでも同じですが、
「値」で検索した場合は、この機能は表示形式に依存した検索となっています。

上図では、
A,B,C列は、入っている値は全て同じで、列幅と表示形式だけの違いだけです。

A列は、表示形式が標準で全桁表示されている
B列は、表示形式が標準で列幅が狭く#####と表示
C列は、表示形式が「#,##0;[赤]-#,##0」カンマの桁区切り

D,E,F列は、入っている値は全て同じで、表示形式だけの違いです。

D列は、表示形式の日付「*2012/3/14」
E列は、表示形式の日付「2012/3/14」
F列は、表示形式のユーザー定義「yyyy/mm/dd」

良くありがちなFindメッソッドの使い方をしたVBAです。


  
    
      Sub test11()

      　　Dim rng As Range

      　　Set rng = Range("A:A").Find(What:=1234567, _

      　　　　　　　　　　　　　　　　LookIn:=xlValues, _

      　　　　　　　　　　　　　　　　Lookat:=xlWhole, _

      　　　　　　　　　　　　　　　　SearchOrder:=xlByColumns, _

      　　　　　　　　　　　　　　　　MatchByte:=False)

      　　If rng Is Nothing Then

      　　　　MsgBox "エラー"

      　　Else

      　　　　MsgBox rng.Address

      　　End If

      End Sub

Range("A:A").Find(What:=1234567,
この部分を変更しつつ確認してみると、
Range("A:A").Find(What:=1234567, → $A$7
Range("B:B").Find(What:=1234567, → エラー
Range("C:C").Find(What:=1234567, → エラー
Range("D:D").Find(What:="2019/1/23", → $D$4
Range("E:E").Find(What:="2019/1/23", → エラー
Range("F:F").Find(What:="2019/1/23", → エラー
Range("D:D").Find(What:="2019/01/23", → エラー
Range("E:E").Find(What:="2019/01/23", → エラー
Range("F:F").Find(What:="2019/01/23", → $F$4
このような結果になります。

LookInとLookatを工夫することで検索することは出来ますが、
セルの状態に合わせてVBAコードを書くことも難しいですし、VBAを見て理解することも難しいでしょう。
ただし、数式で入っているセルを検索する場合は、xlFormulas：数式で検索しても表示形式に依存する為検索できないものがでてきます。
この場合はxlValues：値で表示形式に合わせた検索が必要になります。

このような理由があるので、数値や日付の場合には、Findメソッドを使うにはかなり注意が必要です。
実務では安易には使いづらい機能になります。

また、
先に書いたサンプルのWorksheetFunction.Matchを使う場合、日付の場合には少し工夫が必要です。
セル値でMatch関数を使う場合

WorksheetFunction.Match(Range("H1")~~.Value~~, Columns(4), 0)
この.Valueを付けてしまうとマッチしません。

定数指定でMatch関数を使う場合

WorksheetFunction.Match("2019/1/23", Columns(4), 0)
このようにデータ型が違っていてはエラーとなりマッチしません。

WorksheetFunction.Match(CDate("2019/1/23"), Columns(4), 0)
日付だからといっても、日付型にしてもエラーとなりマッチしません。

WorksheetFunction.Match(CLng(CDate("2019/1/23")), Columns(4), 0)
日付のシリアル値で検索すると正しくマッチします。

値の完全一致で検索した場合は、Findメソッドでは文字列の検索においても表示形式は関係します。
例えば、
「@"様"」といった表示形式を指定していると、元の値ではFindは検索されなくなります。

最後に

このように、Findメソッドを使う場合はかなり注意しなければならず、
特段にFindメソッドを使う理由が見当たらない為、私はほとんどFindを使うことはありません。
もちろん、全く使わないというわけではありませんし、
また、Findメソッドを使う事を否定するつもりもありません。
実務でも、シート全体から特定文字を検索するときなどには使う場合もあります。

VBAとしては習得必須メソッドではありますが、実際はそんなに使う機会が多いものではない、
といいますか、そんなに頻繁に使うべきメソッドではないということです。
つまり、
「VBA Find」で使い方をWeb検索してまで使うようなものではなく、
むしろ、これに時間をかけるくらいなら、
他の方法を模索した方が良いのではかいかと思います。

同じテーマ「マクロVBA技術解説」の記事

記述による処理速度の違い
 速度比較決定版【Range,Cells,Do,For,ForEach】
エクセルVBAのパフォーマンス・処理速度に関するレポート
VBAのFindメソッドの使い方には注意が必要です
マクロVBAの高速化・速度対策の具体的手順と検証
 動的2次元配列の次元を入れ替えてシートへ出力（Transpose）
大量データで処理時間がかかる関数の対処方法（SumIf）
大量データにおける処理方法の速度王決定戦
 遅い文字列結合を最速処理する方法について
 大量VlookupをVBAで高速に処理する方法について
 Withステートメントの実行速度と注意点

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.繰り返し処理（For Next)｜VBA入門
3.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
4.変数宣言のDimとデータ型｜VBA入門
5.RangeとCellsの使い方｜VBA入門
6.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
7.セルのクリア（Clear,ClearContents）｜VBA入門
8.メッセージボックス(MsgBox関数)｜VBA入門
9.条件分岐（Select Case）｜VBA入門
10.ブック・シートの選択（Select,Activate）｜VBA入門

ホーム
マクロVBA応用編
マクロVBA技術解説
VBAのFindメソッドの使い方には注意が必要です

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。

VBA技術解説
VBAのFindメソッドの使い方には注意が必要です