ExcelVBAにて、単数列以外の範囲にMatch関数を使用した際にシステムエラーが発生する

Match関数にて表内を検索したい

ExcelVBAにて、Match関数を使用して表から特定の値が設定されたセルを取得しようと試みました。
しかし、検索対象範囲を単数列とした場合は検索処理が実施されるようなのですが、複数列にしたり、行方向にしたり、表全体を対象としたりした際に1004エラーが発生します。

原因や対処法について、ご存じの方がおいででしたら教えていただきたいです。

該当のソースコード

OKパターン
WorksheetFunction.Match("検索対象文字列", Range("A:A"))

NGパターン1

WorksheetFunction.Match("検索対象文字列", Range("4:4"))

NGパターン2

dim r:r = Worksheets("作業シート").UsedRange
WorksheetFunction.Match("検索対象文字列", ｒ)

補足情報（FW/ツールのバージョンなど）

Office365を使用

追記

パターン2については、多次元配列は未対応なだけ？

NGパターン3

dim r:r = Worksheets("作業シート").UsedRange
WorksheetFunction.Match("検索対象文字列", r.Rows(2))

行方向に取得した場合、結果の値が異常値を返します。
行に存在する値を入力してもエラーが発生したり、値が返る場合も違う要素で同じ値が返ったりします。

行動規範の内容に同意します

回答3件

ベストアンサー

照合の種類を指定してみてください。

VBA
1NGパターン1
2WorksheetFunction.Match("検索対象文字列", Range("4:4"),0)

MATCH関数は単数行列、一次元配列は対応していますが
複数行列、二次元配列以降は対応していません。

ワークシート関数を使っているので、検証のために
直接シートに数式を書き込むとわかりやすいと思います。

Findメソッドを使うとセル位置を特定できます。
条件に当てはまるセルを検索する(Find/FindNext/FindPreviousメソッド)

投稿2020/08/21 00:11

radames1000

総合スコア1925

rot_snow

2020/08/21 09:30

色々試した結果、Matchの第三引数の規定値が1であるらしく、その状態で未ソートのデータを引き渡した結果発生した異常のようでした。ありがとうございます。

行動規範の内容に同意します

WorksheetFunction.Match

は、ワークシート上で使うMatch関数をVBAから呼び出しているにすぎません。
なので、
単一列、単一行のみにしか対応できません。(シート上で確認願います。）
複数列複数行に対応する場合は各各をループする必要があります。

それから、WorksheetFunction内の関数は、計算結果がエラー値の場合は、
コード1行全体がエラーとして返ってきます。
注意してください。

ExcelVBA
1Sub test()
2    Dim r As Range
3    Dim ix As Variant
4    Dim k As Variant
5    Dim p As String
6    
7    p = "見つかりませんでした。"
8    k = InputBox("キーワード入力")
9    For Each r In Worksheets("作業シート").UsedRange.Rows
10        ix = CVErr(xlErrNA)
11        On Error Resume Next
12        ix = WorksheetFunction.Match(k, r, 0)
13        On Error GoTo o
14        If Not IsError(ix) Then
15            r(ix).Select
16            p = r(ix).Address(False, False)　& "にありました。"
17            Exit For
18        End If
19    Next
20    
21    MsgBox p
22End Sub