Excel VBAで配列の途中から(X)Match関数で検索する

Office365です。

WorksheetFunction.(X)Match
でのワイルドカード検索で引っかかった
配列内の行数を取得したいと考えています。

複数該当箇所があるので、先頭及び末尾以外の2件目、3件目、、n-1件目の
行数も取得したいのです。

先にワイルドカードでの共通文言でFilterをかけて、
得られた戻り値の配列の要素を1つずつ
改めてMatchの検索値として検索しても、
Filterの戻り値の配列内に完全に同じValueが存在しているため、
初めの目的の配列に対してMatchで検索するのでは、
やはり同じ値の2件目が取れてこないのです。

究極は目的配列を1要素ずつループを回してIfで検索していってもいいのですが
少し記述が長くなります。

2件目以降を取得するために(X)Matchにおける「次を検索」のような工夫がございましたら
ご教授くださると幸いです。

質問者からの補足コメント

• ご返答ありがとうございます。
  
  >XMATCHは使った事ないですけど複数の値（配列）は返さないですよね？
  そうなんです。
  それを何とかして複数の結果を得たいと思っています。
  配列として戻り値を一括に得たいということではなく、
  ループ中で何度もMatchを用いて個々の数値として得られれば構いません。
  
  ・(X)Matchによって複数検索結果を得る
  
  a1
  b1
  b1
  a2
  b1
  
  なる配列があるとして、
  "*1"
  Matchで検索をかけると、
  2という結果しか得られません。
  逆から検索をかけて5も取得できますが、3は得られない状況です。
  
  ワークシート上なら
  IndexとMatchを組み合わせて、
  Matchの検索範囲をずらすと言う手があると思います。
  
  しかし今回はVBAの配列上を検索範囲とすることになります。
  
  
  Filter云々については、この捕捉欄の文字数の関係で、お礼に記します。

  No.1の回答に寄せられた補足コメントです。 補足日時：2021/11/15 00:13

• ああ、10回まで補足できるのか。
  
  Filter云々については、
  初めに"*1"でFilterに準ずることをすれば、
  得られる配列の要素を「ワイルドカードを含まない具体的な」Matchの検索値
  とすることもできますが、
  今回は全く同じ値b1が複数含まれているので
  それも意味をなさないかな
  ということです。

    補足日時：2021/11/15 00:14

No.5 ベストアンサー 回答者： goomania 回答日時： 2021/11/23 14:51

以下の考えが、ご質問者の意図にそったものかは判りませんが、

(1)Xmatchワークシート関数を使って、配列を逆から検索
(2)検索値が見つかったら、配列のサイズを発見位置より１つ少ない要素数に減らして小さくする
(3)検索値が発見されないか、配列サイズ(要素数)が0になるまでLOOP
というアルゴリズムで考えればよいのではないでしょうか。
この考えでサンプルを作成してみました。

Option Base 1
Sub sample()

'末尾が1の文字セルを探す
Dim r As Long 'Match関数で探す配列のインデックス番号
Dim s As Long 'Match関数の検索対象配列の要素数
Dim ans As String '結果を格納する文字変数
Dim data() As Variant '検索対象データの動的配列を準備

ans = ""
s = 10 '配列の初期サイズを指定
ReDim data(s)
data() = Array("b1", "b1", "a2", "b1", "b1", "a2", "b1", "b1") 'サンプルデータを格納
Do
r = 0 'Match関数で探す配列のインデックス番号を初期化
On Error Resume Next
r = WorksheetFunction.XMatch("*1", data, 2, -1) 'XMatchワークシート関数で指定範囲を逆方向に検索
On Error GoTo 0
If r = 0 Then Exit Do 'XMatchで検索値が見つからなければ終了
ans = ans & r & "番目" & vbCrLf '結果表示に発見した要素のインデックス番号を追加
s = r - 1 '次の検索対象配列のサイズ(要素数)を算出
If s = 0 Then Exit Do 'XMatchで検索対象配列の要素数が0になったら終了
ReDim Preserve data(s) '検索対象配列のサイズ(要素数)を変更
Loop
If ans = "" Then
ans = "検索対象は見つかりませんでした"
Else
ans = "検索対象は" & vbCrLf & vbCrLf & ans & vbCrLf & "に見つかりました"
End If
MsgBox ans
End Sub

上記コードを実行すると、サンプルデータのb1の位置である、１、２、４、5、７、8が取得できます(順番は８、7、5･･･と降順ですが･･･)。

また、Xmatch関数の第一引数、"*1"を"*2"に変更すると、a2の位置である3、6が取得できます。

この回答へのお礼

すみません、アカウントが半分死んでいてお礼が大変に遅くなりました。
ありがとうございました。
確かに。この方法が一番シンプルだったかもしれません。

お礼日時：2022/07/17 04:04

No.6 回答者： goomania 回答日時： 2021/11/23 14:56

No.5です。

投稿したVBAのコメントに勘違いがありました。

'末尾が1の文字セルを探す

という部分がありますが、探すのは「セル」ではなく「配列要素」ですので、

'末尾が1の文字配列要素を探す

に読み替えしてください。

この回答へのお礼

丁寧にありがとうございました。

お礼日時：2022/07/17 04:04

No.4 回答者： ママチャリ 回答日時： 2021/11/18 17:28

最初に見つけた要素に、”済”みたいな文字を代入して、潰した上で次の検索を行うのはどうでしょう？

配列の中身を変更したくないのであれば、別の作業用変数に格納した上で潰せば、影響は無いように思います。

この回答へのお礼

すみません、アカウントが半分死んでいてお礼が大変に遅くなりました。
ありがとうございました。
なるほど、考えもつかなかったです。配列をコピーしてなら可能ですね。

お礼日時：2022/07/17 04:01

No.3 回答者： Qchan1962 回答日時： 2021/11/16 13:30

こんにちは、

新しい組み込み関数の利用方法を検証しておられるものと思いますが、
VBAであれば、Private Functionを作る方が簡単かな、、と思います。

この回答へのお礼

すみません、アカウントが半分死んでいてお礼が大変に遅くなりました。
ありがとうございました。
ですよね、素直にそう言った別法に頼りました。

お礼日時：2022/07/17 03:59

No.2 回答者： めぐみん_ 回答日時： 2021/11/15 10:50

配列の元が何であるかわかりませんが、セルの値って事ならFIND FINDNEXTでなら移動可能ですよね。

そう言うのが配列に対してあるのかなって考えてみましたが、ん～初級レベルなジジィには厳しいかな。

重複しないよう取得したいってならパワークエリ（未経験）とか、ＢｏｏｋへのＡＤＯ接続によるＳＱＬ文での条件付き抽出とかはあるのかもですけど。
でも大袈裟になるかな？

この回答へのお礼

確かにSQLでなら1発で複数レコードとして得られますよね。レコードの数だけ(x)MATCHで検索するより断然よいですね。

お礼日時：2022/07/17 03:57

No.1 回答者： めぐみん_ 回答日時： 2021/11/14 16:37

どう言う事をしてて躓いているのかがちょっと不明っぽい。

XMATCHは使った事ないですけど複数の値（配列）は返さないですよね？

Fillterによって配列を作成したってなら、
http://officetanaka.net/excel/vba/function/Filte …
一次元で0スタートならUBound(Tokyo) - 1が要素数になると思えるのですが、そう言う訳ではないのでしょうか？

この回答へのお礼

すみません、アカウントが半分死んでいてお礼が大変に遅くなりました。
ありがとうございました。

お礼日時：2022/07/17 03:58