VBA-SVERWEIS mit variablen Dateinamen
02.05.2019 10:58:43
Paul
ich hatte die Frage bereits gestellt und auch eine super Antwort erhalten. Jedoch verstehe ich nicht wie ich in dem alten Beitrag nun antworten kann. (garnicht möglich sobald er im Archiv ist?)
ich will mit einem SVERWEIS Werte aus anderen Excel-Dateien (Quelldateien) ziehen. Welche Dateien durchsucht werden sollen ist abhängig von der jedweiligen Angabe in D3:D21.
Konkret:
Ziel-Excel:
In A3:A21 stehen die Suchkriterien.
In H3:H21 sollen die gefundenen Werte aus den Quelldateien eingetragen werden.
D3:D21 geben einen Hinweis auf die Namen der Quelldateien, welche mit dem SVERWEIS durchsucht werden sollen:
test1
test2
etc.
Quelldateien:
sind alle im Ordner: C:\Users\xxx\Desktop\Test\
Die Dateinamen ähneln sich vom Aufbau:
Info_test1.xlsx
Info_test2.xlsx
etc.
Die Matrix in den Quelldateien ist A4:C27, Spaltenindex 3.
Vielen Dank erstmal an Rob für folgende Lösung:
Option Explicit
Sub LookupValues()
Dim r As Range
Dim wbLookup As Workbook, wbDestiny As Workbook
Dim searchRange As Range
Dim searchValue As Variant
Application.ScreenUpdating = False
On Error GoTo Errhandler
Set wbDestiny = Workbooks("Paul.xlsm") 'HIER NAME DER ZIELDATEI ENTSPRECHEND ÄNDERN
'HINWEIS QUELLDATEIEN R DURCHSCHLEIFEN
For Each r In wbDestiny.Sheets(1).Range("D3:D21")
searchValue = r.Offset(0, -3).Value
Workbooks.Open "C:\Users\admin\Desktop\Info_" & r & ".xlsx" 'HIER ZIELPFAD ANPASSEN
Set wbLookup = Workbooks("Info_" & r & ".xlsx")
Set searchRange = wbLookup.Sheets(1).Range("A4:C27")
r.Offset(0, 4).Value = Application.VLookup(searchValue, searchRange, 3, False)
wbLookup.Close savechanges:=False
Next r
Application.ScreenUpdating = True
Exit Sub
Errhandler:
MsgBox Err.Description, vbCritical
End Sub
1. Die Funktion soll nur im Tabellenblatt A (Paul.xlsm) ausgeführt werden und nicht in allen.
2. Es wird immer nur der letzte gefundene Wert angezeigt, die anderen liefern wegen der INDIREKT Funktion jedoch den Fehler #BEZUG! (da die Dateien ja nicht mehr offen sind) --> können die gefundenen Werte fest eingetragen werden?
3. Die Funktion soll standardmäßig beim öffnen der Datei ausgeführt werden-
4. die Funktion soll nur ausgeführt werden für Zeilen in denen in D3:D21 nicht "Projekt" steht.
Schönen Tag,
Paul