別のワークブックで値を検索/見つけるにはどうすればよいですか？

VLOOKUP関数で保存パス、ファイル名、およびワークシートを設定するのは少し混乱することがあります。この方法では、それを簡単に解決するためのVBAを紹介します。

1. Alt + F11キーを押して、Microsoft Visual Basic for Applicationsウィンドウを開きます。

2. 「挿入」>「モジュール」をクリックし、以下のVBAコードを開いているモジュールウィンドウに貼り付けます。

VBA: 閉じた別のワークブックからデータをVLOOKUPし、値を返す

Private Function GetColumn(Num As Integer) As String
If Num <= 26 Then
GetColumn = Chr(Num + 64)
Else
GetColumn = Chr((Num - 1) \ 26 + 64) & Chr((Num - 1) Mod 26 + 65)
End If
End Function
Sub FindValue()
Dim xAddress As String
Dim xString As String
Dim xFileName As Variant
Dim xUserRange As Range
Dim xRg As Range
Dim xFCell As Range
Dim xSourceSh As Worksheet
Dim xSourceWb As Workbook
On Error Resume Next
xAddress = Application.ActiveWindow.RangeSelection.Address
Set xUserRange = Application.InputBox("Lookup values :", "Kutools for Excel", xAddress, Type:=8)
If Err <> 0 Then Exit Sub
Set xUserRange = Application.Intersect(xUserRange, Application.ActiveSheet.UsedRange)
xFileName = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx", 1, "Select a Workbook")
If xFileName = False Then Exit Sub
Application.ScreenUpdating = False
Set xSourceWb = Workbooks.Open(xFileName)
Set xSourceSh = xSourceWb.Worksheets.Item(1)
xString = "='" & xSourceWb.Path & Application.PathSeparator & _
"[" & xSourceWb.Name & "]" & xSourceSh.Name & "'!$"
For Each xRg In xUserRange
Set xFCell = xSourceSh.Cells.Find(xRg.Value, , xlValues, xlWhole, , , False)
If Not (xFCell Is Nothing) Then
xRg.Offset(0, 2).Formula = xString & GetColumn(xFCell.Column + 1) & "$" & xFCell.Row
End If
Next
xSourceWb.Close False
Application.ScreenUpdating = True
End Sub

注意：このVBAは、選択した列の2列後ろの列に値を返します。例えば、このVBAを適用するときに列Bを選択すると、値は列Dに返されます。目的の列を変更する必要がある場合は、次のコードを見つけます： xRg.Offset(0, 2).Formula = xString & GetColumn(xFCell.Column + 1) & "$" & xFCell.Row そして、これを他の数字に置き換えます。 2 必要に応じて他の数字に変更してください。

3. F5 キーを押すか、「実行」ボタンをクリックして、このVBAを実行します。

4. 開いたダイアログボックスで、検索するデータ範囲を指定し、「OK」ボタンをクリックします。 OK ボタンをクリックします。

5. 開いている「ワークブックの選択」ダイアログボックスで、値を検索するワークブックを選択し、「開く」ボタンをクリックします。 開く ボタンをクリックします。

これで、選択されたすべての値が指定された閉じたワークブックで検索され、対応する値が指定された列に返されます。スクリーンショットをご覧ください：