Application.Matchについて

質問

A1と同じものを検索するのにApplication.MatchとForNextを使って行いたいのですが上手く出来ません
A1　7000 　B1　500
　　　　　　　B2　250
　　　　　　　B3　7000
　　　　　　　B4　350
　　　　　　　B5　7000　
　　　　　　　B6　650
　　　　　　　B7　7000
　　　　　　　B8　870
　　　　　　　B9　900
　　　　　　　B10　340
とあって下のマクロを実行すると一番最初のしかでませんあるもの全部を検索したいのですがどうすれば良いですか教えて下さい
Sub Sample3()
Dim ret As Variant
For i = 1 To 10
ret = Application.Match(Range("A1"), Range("B1:B10"), 0)
Cells(2, 5) = ret
Next
If IsError(ret) Then
MsgBox "該当データが見つかりません"
Else
MsgBox ret & "番めのデータです"
End If
End Sub

回答No.3

tsubuyuki · ベストアンサー

よそ様の回答への補足に横槍を指させていただきます。
ご容赦ください。

> ForNextでは遅いのではと思っています。
２番さんの回答で提示くださっているコードは試してみましたか？

当方の環境（Ｄ社のごく一般的なビジネスモデルＰＣ）で、
「Ｂ列に列記してある１０万行のデータから
　Ａ１セルと合致するものをＤ列に、そのデータがある行をＥ列に、
　それぞれ列記していくコード」
を思いつくまま書いたものが以下のコードです。

Sub test()
Dim TRow As Long
Application.ScreenUpdating = False
Range("D:E").ClearContents
TRow = 0
For i = 1 To Cells(Rows.Count, 2).End(xlUp).Row
If Range("B" & i) = Range("A1") Then
TRow = TRow + 1
Range("D" & TRow) = Range("B" & i)
Range("E" & TRow) = i
End If
Next
If TRow = 0 Then
MsgBox "該当なし"
End If
Application.ScreenUpdating = True
End Sub

これで１０万件から該当約１００件を書き出すのに２．５秒くらいです。
これを遅いとみるか早いとみるかは個人の判断ですが、
少なくとも「永遠に最初の１件しか見つけないＭＡＴＣＨ関数を使う方法」よりは
確実に早いことだけは間違いないです。

単純に「検索」するだけなら、Findメソッドが早くて楽です。
順に「抽出」して書き出すなら、For～Nextが早いです。
ワークシート関数を使うなら、更なる工夫が必要です。
（例えば、見つけた行より下からまた探すように範囲を決めてFor～Nextなど）

> Application.WorksheetFunctionなんかを使用してできますか？
エクセルのワークシート関数で「合致した全ての値を返す関数」
あるいは「再計算すると合致したデータの内、２番目に合致した値を返す関数」
もしくはそれに代替できる「関数を使った機能」
と言うのが私の知識の中には無いので、上記の工夫以外には残念ながら思いつきません。

質問文中のコードについて

> For i = 1 To 10
> ret = Application.Match(Range("A1"), Range("B1:B10"), 0)
> Cells(2, 5) = ret
> Next

とりあえず、ここまで
ワークシート関数のＭＡＴＣＨを使って同じ範囲を検索している間は
何度繰り返そうが最初に合致したデータしか取りません。
つまり、変数retの値は「最初に合致した行数」「エラー 2042=値を取得できない」
この二つのどちらかしか持ちません。

なのでこの後で

> If IsError(ret) Then
> MsgBox "該当データが見つかりません"
> Else
> MsgBox ret & "番めのデータです"
>End If

としても、最初のデータ行数しか返さない、という事です。

仮に上部のFor～Nextで上から徐々に舐めていくように組めたとしても、
見つける度に変数retを書き換えて、
全てのデータを見終わった後でメッセージボックスを出しているのですから、
この場合も「最後のデータ行」「エラー」どちらかしか表示されません。
これは「お望みの処理」ではないはずです。

それよりもまず先に、余計なお世話かもしれませんが
「ご自身がやりたい作業はどんなものか？」
コレを整理なさった方がよろしいのではないでしょうか。

お礼

よく判りました。
もともとNo2の回答で私のやりたいことはできたのですが、前に自分でfornextで行った時に時間がかかった記憶がありましたので書かさせて頂いたのですが、これで１０万件から該当約１００件を書き出すのに２．５秒くらいです。これは早いですこれぐらいで出来れば満足です。
これを使わせて貰いたいと思います。丁寧な説明と適確なご指摘ありがとうございました。

回答No.2

web2525

>ForNextを使って行いたい
ならば、こんなかんじかな？

Sub Sample3()
Dim ret As Variant
For i = 1 To 10
If Cells(1, 1) = Cells(i, 2) Then
ret = ret & i & vbCrLf
End If
Cells(2, 5) = ret
Next
If ret = "" Then
MsgBox "該当データが見つかりません"
Else
MsgBox ret & "番めのデータです"
End If
End Sub

補足

早々ありがとうございました。
>ForNextを使って行いたいならば、こんなかんじかな？ですがこれ以外にApplication.WorksheetFunctionなんかを使用してできますか？　ForNextでは遅いのではと思っています。
実際には沢山の中から検索したいのでできるだけ早いほうが助かります。
再度よろしくお願いします。

回答No.1

web2525

Application.Match(Range("A1"), Range("B1:B10"), 0)
↓
B1:B10の範囲内にA1のデータと同じ物がいくつあるかを調べる

これは何回実行しても3しか出て来ません、B3セルが該当しているという意味でもありません