VBAでのMATCH関数の使用

質問

現在，VBAにてシートの比較プログラムを行っておりますが，その中で検索範囲を示すRangeに名前を使用したいのですがエラーでうまくいきません。

エラーは
「実行時エラー：1004
WorksheetFunctionクラスのMatchプロパティが取得できません」
とでます。

記述は

Application.WorksheetFunction.Match(Cells(r, 132), Range("リース型具Key1"), 0)

としており，”リース型具Key1”が事前に名前登録してある名前です。

方法をご存知の方，よろしくお願いします。

回答No.3

nishi6 · ベストアンサー

シートが異なるとは思ってもいませんでした。

例えば、Cells(r, 132) が Sheet2 にあれば、下記のようにすればいいはずです。
シートを指定していなければ、マクロを実行したシートのCells(r, 132)になっているはずです。

Application.WorksheetFunction.Match(Worksheets("Sheet2").Cells(r, 132), Range("リース型具Key1"), 0)

それから、Match関数で結果が求まる場合は問題ありませんが、結果が求まらない場合の処理も考慮すべきでしょう。下記は参考例です。

Sub myTest01()
　　Dim shtNo As Integer
　　　　shtNo = 2
　　Dim ws2 As Worksheet
　　Dim ws3 As Worksheet
　　　　Set ws2 = Worksheets("Sheet" & shtNo)
　　　　Set ws3 = Worksheets("Sheet3")

　　On Error GoTo ErrorTrap

　　MsgBox Application.WorksheetFunction.Match(ws2.Cells(2, 2), _
　　　　　　　　　　　　　　　　　　　　　　 ws3.Range("リース型具Key1"), 0)
　　'
　　'// 色々な処理 //
　　'
　　Set ws2 = Nothing
　　Set ws3 = Nothing

　　Exit Sub

ErrorTrap:
　　MsgBox ws2.Cells(2, 2) & "は見つかりません。"
　　Resume Next
End Sub

お礼

nishi6さん
どうやら私の理解不足で，On Errorを考慮していないとMATCHしなかったときにエラーになることが判りました。ご指摘のエラー文を入れたことでOKになりました。有難う御座いました。

補足

nishi6さん，さっそく有難う御座います。
ご指摘の方法で試しました。うーん，うまくいきません。
Match(ws3.Cells(2,2),ws3.Range("リース型具"）・・のように名前が参照する範囲セルのシートと同様のシートのCellを指定するとOKですが，ws2.Cellsとしたら同様のエラーが発生します。仕様なのでしょうか。
ちなみに，アクティブシートをws2にしていることは関係ないでしょうか。

回答No.2

nishi6

Cells(r, 132)が『リース型具Key1』のどの列を見ているかが問題です。

『リース型具Key1』の一番左の列を見ているなら、
　　Application.WorksheetFunction・・・・・・ Range("リース型具Key1").Columns("A:A"), 0)

左から２列目を見ているなら、
　Range("リース型具Key1").Columns("B:B")

とします。３列目以降も同様に指定します。

補足

ご回答有難う御座います。大変参考になり，さっそく試してみましたが，やはりエラーは解消されませんでした。いろいろ行ってみた結果，Cells(r,132）のシートと”リース型具Key1”を示す範囲のシートが異なる場合にNGとなります。Cells(r,132)を”リース型具Key1”が定義されている同一シートに変更するとOKでした。シート間での名前の参照はサポートされていないのでしょうか。

回答No.1

tsukasa-12r

Range("リース型具Key1") って、A1:B10 のように複数列になってませんか？そういう仕様なのかよくわかりませんが、A1:A10 のような１列のみに範囲だったら大丈夫ですが、複数列の範囲だとエラーになるようです。（名前で参照するか "A1:A10" のような範囲指定で参照するかは関係ないようです。）