VBAでのXLOOKUP関数の記載方法について

前提・実現したいこと

エクセル・VBAの超初心者です。
VBAにXLOOKUP関数を使用して、別シートへデータを参照したいと思っています。
作成しているエクセルデータでは、元データシート「Sheet2」に

No 商品名　産地
116 ケーキ　ロシア
117 クッキー　フランス
118 アイス　インドネシア
…
というデータがあり、
出力データシート「出力シート」のセル「A2」にNoを入力し、セル「B2」に商品名、セル「C2」に産地のデータを参照しようとしています。

発生している問題・エラーメッセージ

実行時エラー 9 インデックスが有効範囲にありません。

該当のソースコード

コードは以下の通りです。

Sub TEST2()

'XLookup関数で、別シートを参照する
Worksheets("出力シート").Range("B2").Value = WorksheetFunction.XLookup(Worksheets("出力シート").Range("A2").Value, Worksheets("Sheet2").Range("A:C"), Worksheets("Sheet2").Range("A:C"), """", 0, 1)

End Sub

試したこと

XLOOKUPでのVBA利用について自分なりに類似例を調べて記載してみたのですが、なかなかエラーから抜け出せず、非常に困っております。
（なお引数(見つからない場合)について””だけ入力すると、
実行時エラー 1004 worksheetfunctionクラスのXlookupプロパティを取得できません
と表示されました）

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

元々VLOOKUPでデータ参照しようと考えていましたが、実際に運用を検討しているデータ形式では一行に20近いセルデータがあるため、XLOOKUPを利用したいと考えています。

最近エクセル・VBAの勉強を始めた超初心者で、なかなかXLOOKUPのVBA使用実例を探し当てることもできず、苦戦しております。（おそらく単純な構文ミスによるエラーなのかと考えておりますが…）
VBAを使えるまで根気強くトライしていきたいと思っており、ご教示時いただけますと幸いです。
どうぞよろしくお願いいたします。

前提・実現したいこと

発生している問題・エラーメッセージ

実行時エラー 9 インデックスが有効範囲にありません。

該当のソースコード

コードは以下の通りです。

Sub TEST2()

'XLookup関数で、別シートを参照する
Worksheets("出力シート").Range("B2").Value = WorksheetFunction.XLookup(Worksheets("出力シート").Range("A2").Value, Worksheets("Sheet2").Range("A:C"), Worksheets("Sheet2").Range("A:C"), """", 0, 1)

End Sub

試したこと

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

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

回答1件

ベストアンサー

引数を見ると、
「検索範囲」「戻り範囲」ともに、「Worksheets("Sheet2").Range("A:C")」となっていますね。ここが原因かと。
検索範囲はA列で、戻り範囲はB列かと。
また、見つからない場合の処理ですが、私の環境ではダブルクォートではなく「vbNullString」を指定すると、空欄になりました。

投稿2021/09/07 07:32

hex309

総合スコア761

yukiyuki1203

2021/09/08 06:29

ご回答いただきありがとうございます。「検索範囲」「戻り範囲」をA列・B列に指定すると動きました。 (私の環境では””でも「vbNullString」でも処理はできました) ただスピルにしたくて「戻り範囲」をB：Cに指定すると動かない(エラーは出ず処理されるけれど何も参照されない)なってしまうのですが、他にもなにか不足があるのでしょうか。コードは以下です。 Worksheets("出力シート").Range("B2").Value = WorksheetFunction.XLookup(Worksheets("出力シート").Range("A2").Value, Worksheets("Sheet2").Range("A:A"), Worksheets("Sheet2").Range("B:C"), vbNullString, 0, 1) 何度も恐縮ですが、どうぞよろしくお願いいたします。

hex309

2021/09/08 07:11

私もちょっと調べながらなので、確証がない部分もありますが、以下、ご参考まで。まず、 Worksheets("出力シート").Range("B2").Value ですが、 Worksheets("出力シート").Range("B2:C2").Value ですね。関数をワークシートに入力するのとは異なって、自動的に隣のセルに入力とは行かないようです。また、 WorksheetFunction.XLookup(Worksheets("出力シート").Range("A2").Value, Worksheets("Sheet2").Range("A:A"), Worksheets("Sheet2").Range("B:C"), vbNullString, 0, 1) を、TypeName関数で調べると「Range」が返されました。そこで、 WorksheetFunction.XLookup(Worksheets("出力シート").Range("A2").Value, Worksheets("Sheet2").Range("A:A"), Worksheets("Sheet2").Range("B:C"), vbNullString, 0, 1).Value と、最後に「.Value」を加えたところ、セルB2とC2に値が入力されました。以上となります。

yukiyuki1203

2021/09/09 03:19

ご指摘いただいた箇所を修正するとスピルで返されました。ご教示いただきありがとうございます！もう１点だけ、何度も恐縮ではございますがTypeName 関数での調べ方を教えていただけますと幸いです。今後エラー時に少しでも自分で調べられればと思います。今回WorksheetFunction.XLookup(Worksheets("出力シート").Range("A2").Value, Worksheets("Sheet2").Range("A:A"), Worksheets("Sheet2").Range("B:C"), vbNullString, 0, 1)に「.Value」指定が不足していた内容を確認するにあたり、関数の使い方としては単純に Dim MyType MyType = TypeName(WorksheetFunction.XLookup(Worksheets("出力シート").Range("A2").Value, Worksheets("Sheet1").Range("A:A"), Worksheets("Sheet2").Range("B:E"), "", 0, 1)) Debug.Print TypeName(MyType) で良いのでしょうか？(結果実行時エラー9でインデックスが有効範囲にないと出てしまいました。。。) 何度も質問を返してしまい大変恐縮ではございますが、どうぞよろしくお願いいたします。

hex309

2021/09/09 03:31

まず、エラーになってしまったコードですが、対象のシートはきちんと存在した状態でしたか？エラーメッセージから、参照しているシートが無いように思われます。また、使い方ですが、提示されたコードですと、TypeName関数の結果を変数MyTypeに代入していますよね。内容を確認したいのであれば、単純に以下でOKです。 Debug.Print MyType 次のようにしてしまうと、変数MyTypeの種類を調べることになります。 Debug.Print TypeName(MyType) 以下、ご参考まで。 Debug.Print TypeName(MyType)

yukiyuki1203

2021/09/10 01:01

ご回答ありがとうございます。エラーについては、同じシート名"Sheet1"を含む別ブックを同時に開いていたため発生したようでした(閉じると反映されました)。変数としての扱いなど、以後注意して学んでいきたいと思います。何度もご丁寧にご回答・ご説明いただき本当にありがとうございました！

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