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")
です。