xlookup関数の引数を利用して検索元に移動するVBAコードについて

Question

sheet2のa1:a10には支店番号、b1:b10には支店名のデータが入力されています。sheet1のa1セルに支店番号を入力するとxlookup関数を用いて支店名がsheet1のb1セルに表示されるようになっています。そのsheet1の支店名が表示されているセルをクリックするとsheet２の検索元のセルに移動するvbaコードを教えてください。
chatgptに聞いても作動できるコードにたどり着きませんでした。

tatsumaru77 · Accepted Answer

No5です。
No5のマクロはスピルされたセルには対応していませんでしたが、
スピルされたセルに対応したものが、こちらになります。
下記URLにアップしました。
https://ideone.com/FT9VYb
こちらの環境ではxlookupが動作しませんので、動作確認は行っていません。
期待した動作をしないかもしれませんので、ご了承ください。

tatsumaru77 · Answer

丁寧な補足ありがとうございました。
こちらでは、xlookupの機能を実現できませんので、回答を控えようかと思いましたが、事情が分かりましたので、参考資料として、回答致します。

こちらに投稿するとエラーになるので、下記URLにアップしました。
https://ideone.com/D0gBQY

当方、xlookupが実行できる環境を持っていませんので、正確な動作確認は行っておりません。

１４行目に
str = Target.Formula2
がありますが、このあと、
str = "=XLOOKUP(A1,Sheet2!A1:A10,Sheet2!B1:B10)"
を一時的に挿入し、動作確認しました。
もし、エラーになる場合は、
str = Target.Formula2を
str = Target.Formula
に変えてみてください。
尚、スピル機能には対応できていません。
Sheet1のB1をダブルクリックしたケースが本件ですが、
例として、
Sheet1のC1にスピルされたデータがあり、C1をダブルクリックしても、
Sheet2のC1に移動はしません。

プログラムの大まかな流れは以下のようになっています。
１．ダブルクリックされたセルに以下の計算式があれば、処理する。
無ければ、終了。
=XLOOKUP(任意の文字列)

２．XLOOKUPの各パラメータを取得
第１パラメータは、検索用の文字列
第２パラメータは、検索範囲
Sheet2!A1:A10
のようになっている前提です。
第3パラメータは、取得範囲
Sheet2!B1:B10
のようになっている前提です。
（このシート名は第２パラメータのシート名と同じ前提です）
第４パラメータ以降は、無視します。
（検索は完全一致の前提です）

３．第１パラメータの文字で、第２パラメータのシートの、検索範囲を
検索します。
（例では　Sheet1のA1:A10を第１パラメータの文字で検索します。）

一致したセルがあった場合、
第２パラメータのシートの下記セルに移動します。
セルの行：一致したセルの行番号と同じ行
セルの列：第３パラメータの取得範囲の最初の列
（例　Sheet2!B1:D10のように定義されていた場合、B列）

tatsumaru77 · Answer

No3です。
>目的は達成するのですが、どうしてもxlookup関数の引数を利用したコードを知りたいです。

申し訳ございません。
当方、xlookupが動作するexcelを持っていませんので（当方office2019です）、マクロからxlookupを呼び出すことはできません。
その為、マクロからxlookupを呼び出すことを期待されているのでしたら、私の方からは回答できません。

参考までにお伺いしたいのですが、
何故、「xlookup関数の引数を利用したコード」を知りたいのでしょうか。
その理由、背景などを提示していただけると、良い回答が得られやすくなるかと思います。

tatsumaru77 · Answer

＞ダブルクリックであればどのようなコードになるのか教えてもらえると助かります！

Sheet1のシートモジュールに以下のマクロを登録してください。

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim code As String
    Dim wrow As Long
    If Target.address <> "$B$1" Then
        Exit Sub
    End If
    code = Range("A1").Value
    With Worksheets("Sheet2")
        For wrow = 1 To 10
            If code = .Cells(wrow, 1).Value Then
                .Activate
                .Cells(wrow, 2).Select
                Exit Sub
            End If
        Next
    End With
End Sub

fujillin · Answer

No1です。

＞ダブルクリックであればどのようなコードになるのか～
VBAで行うのなら、シートに設定されている関数はあまり関係なく、独自に再度検索をして、該当セルをアクティブにすることになると思います。

1）ダブルクリックのイベントで処理を行うにはこんな感じ
https://vba-create.jp/vba-event-doubleclick/

2）対象セルを検索するには、
　・Ａ1セルの値をFINDメソッドで検索するか
　・シート関数のMACH関数を利用して検索する
方法が考えられます。
（プリミティブにループでセル範囲を比較しても良いです）
https://learn.microsoft.com/ja-jp/office/vba/api/excel.range.find
https://learn.microsoft.com/ja-jp/office/vba/api/excel.worksheetfunction.match

3）検索したセルをアクティブにする
https://www.officepro.jp/excelvba/cell_select/index1.html

という流れで、実現できると思います。

fujillin · Answer

こんにちは

セルのダブルクリックであれば、VBAのイベントに用意されているのでさほど難しくはありませけれど、シングルクリックを検知するのにはwin APIを利用することが必要になるので、それなりに面倒な処理になります。
どうしてもご質問文のままを実現したければ、以下のような方法によることになるでしょう。
https://excel-excel.com/tips/vba_151.html
https://liclog.net/mouse_event-function-vba-api/
※ 上記はクリックの検知だけなので、その位置からセル位置を割り出して・・と処理を行うことになります。

一方で、ご質問の目的を達成できれば他の方法でも良いのであれば・・
HYPERLINK関数を利用して、リンク先を検索先のセルに指定することでも可能と思います。
具体的には、Sheet1のＢ1セルに、
=HYPERLINK("#Sheet2!B"&MATCH(A1,Sheet2!A1:A10,0), VLOOKUP(A1,Sheet2!A1:B10,2,0))
の数式を入力することでできるでしょう。
※ 当方の環境ではXLOOKUPが使えませんので、上式はVLOOKUPに変えてあります。

xlookup関数の引数を利用して検索元に移動するVBAコードについて

No5です。

丁寧な補足ありがとうございました。

No3です。

＞ダブルクリックであればどのようなコードになるのか教えてもらえると助かります！

No1です。

こんにちは

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング