Wie kann man Werte in einer anderen Arbeitsmappe nachschlagen/finden?

Es kann etwas verwirrend sein, den Speicherpfad, den Dateinamen und das Arbeitsblatt in der SVERWEIS-Funktion zu konfigurieren. Diese Methode stellt ein VBA vor, um es einfach zu lösen.

1. Drücken Sie die Tasten Alt + F11, um das Microsoft Visual Basic for Applications-Fenster zu öffnen.

2. Klicken Sie auf Einfügen > Modul, und fügen Sie den folgenden VBA-Code in das geöffnete Modulfenster ein.

VBA: Daten mit SVERWEIS aus einer geschlossenen Arbeitsmappe abrufen

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

Hinweis: Dieses VBA gibt Werte in einer Spalte zurück, die 2 Spalten hinter der ausgewählten Spalte liegt. Zum Beispiel, wenn ich Spalte B beim Anwenden dieses VBA auswähle, werden Werte in Spalte D zurückgegeben. Wenn Sie die Zielspalte ändern müssen, finden Sie bitte den Code heraus xRg.Offset(0, 2).Formula = xString & GetColumn(xFCell.Column + 1) & "$" & xFCell.Row und ersetzen Sie ihn durch 2 andere Zahl je nach Bedarf.

3. Drücken Sie die Taste F5 oder klicken Sie auf die Schaltfläche Ausführen, um dieses VBA auszuführen.

4. Geben Sie im sich öffnenden Dialogfeld den Datenbereich an, in dem Sie suchen möchten, und klicken Sie auf die OK Schaltfläche.

5. Wählen Sie nun bitte die Arbeitsmappe aus, in der Sie Werte im sich öffnenden Dialogfeld „Arbeitsmappe auswählen“ suchen möchten, und klicken Sie auf die Öffnen Schaltfläche.

Jetzt werden alle ausgewählten Werte in der angegebenen geschlossenen Arbeitsmappe nachgeschlagen, und die entsprechenden Werte werden in der angegebenen Spalte zurückgegeben. Siehe Screenshot: