Home » エクセルマクロ・Excel VBAの使い方 » WorksheetFunction » VBAで最後尾のシートからVlookup

VBAで最後尾のシートからVlookup

動作検証バージョン:Windows版Excel(バージョン1811 ビルド11029.20108)

「マクロ vlookup 一番右のシートを参照」
という検索キーワードで、このサイト『インストラクターのネタ帳』へアクセスがありました。

Excel VBAで、WorksheetFunctionオブジェクトのVlookupメソッドを使って、一番右・最後尾のワークシートからデータを取得するには、どのようなコードを書けばいいのかを探していた方による検索でしょうか。

[スポンサードリンク]

先頭のワークシートからVlookupするサンプルマクロ

まずは簡単な、一番左・先頭のワークシートからVlookupするコードを理解しましょう。

下図のようにWorksheetFunction.Vlookupメソッドで参照するワークシートが、一番左にある状態で、

VBAで最後尾のシートからVlookup

アクティブシートのA1セルにキーが入力されているときに、以下のSubプロシージャを実行すると、A1セルに入力されているキーを、先頭のワークシートで調べて該当する値がB1セルに入力されます。

VBAで最後尾のシートからVlookup

Sub 先頭のワークシートからVLookup()
 Dim tbl As Range
 Set tbl = Worksheets(1).Range("A1:B7")

 Dim key As Long
 key = Range("A1").Value

 On Error Resume Next
 Dim ret As String
 ret = WorksheetFunction.VLookup(key, tbl, 2, False)
 On Error GoTo 0

 Range("B1").Value = ret

End Sub

WorksheetFunctionオブジェクトのVlookupメソッドで、第2引数を別シート上のセル範囲を参照するよう指定すれば、別のシートからVlookupできます。

この第2引数を、
  ret = WorksheetFunction.VLookup(key, tbl, 2, False)
一番左・先頭のワークシートを参照する式にすれば、一番左・先頭のワークシートからVlookupできます。それが、
  Set tbl = Worksheets(1).Range("A1:B7")
です。

最後尾のワークシートからVlookupするサンプルマクロ

上述の、Worksheetsプロパティの引数(実際にはWorksheetsコレクションの既定プロパティの引数、もっと厳密にいうならWorksheetを単独のオブジェクトとして持つSheetsコレクションの既定プロパティの引数)「1」を、ワークシート総数にした「Worksheets(ワークシート総数)」という式にすれば、一番右・最後尾のワークシートを取得できます。

Sub 最後尾のワークシートからVLookup()
 Dim tbl As Range
 Set tbl = Worksheets(Worksheets.Count).Range("A1:B7")

 Dim key As Long
 key = Range("A1").Value

 On Error Resume Next
 Dim ret As String
 ret = WorksheetFunction.VLookup(key, tbl, 2, False)
 On Error GoTo 0

 Range("B1").Value = ret

End Sub

WorksheetsコレクションのCountプロパティで、ワークシートの総数を取得できます。
ですから、オブジェクト式「Worksheets(Worksheets.Count)」で、一番右・最後尾のワークシートを表すオブジェクトを取得できます。

例えば、
  MsgBox Worksheets(Worksheets.Count).Name
を実行すれば、一番右・最後尾のワークシートの名前がメッセージボックスに表示されます。

この「Worksheets(Worksheets.Count)」というオブジェクト式で、一番左・先頭のワークシートを表すオブジェクトを取得しているのが、
  Set tbl = Worksheets(Worksheets.Count).Range("A1:B7")
です。

最終更新日時:2020-02-06 13:25

[スポンサードリンク]

Home » エクセルマクロ・Excel VBAの使い方 » WorksheetFunction » VBAで最後尾のシートからVlookup

「WorksheetFunction」の記事一覧

検索


Copyright © インストラクターのネタ帳 All Rights Reserved.

.