Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

INDEX MATCH in VBA mit unterschiedlichen Dateien

INDEX MATCH in VBA mit unterschiedlichen Dateien
31.01.2018 15:04:34
Steffen
Hallo, da ich noch neu bin hier im Forum, bitte ich meine evtl. schlechte Problemdarstellung zu entschuldigen.
Ich möchte in einem Excel-Tabellenblatt (Testmappe, Tabelle1) per INDEX und MATCH Zahlen bzw. Namen aus anderen Excel-Dateien extrahieren. Diese sind immer in der vierten Tabelle der jeweiligen Datei, die Tabellen haben aber unterschiedliche Namen.
So lautet der Excel-Befehl, wenn ich ihn ohne VBA schreibe:
=INDEX([Datenmappe.xlsx]BspBlatt!$B$5:$N$13;VERGLEICH($C$3;[Datenmappe.xlsx]BspBlatt!$B$5:$B$13;0); VERGLEICH(D$5;[Datenmappe.xlsx]BspBlatt!$B$4:$N$4;0))
Dieser Befehl funktioniert auch wunderbar.
Nach längerem Suchen und Probieren habe ich diesen Code geschrieben. In diesem Beispiel suche ich nach einem Datum (ich wollte den Befehl oben in VBA umsetzen).

Sub Zusammenfuehren()
Dim oTargetSheet As Object
Dim oSourceBook As Object
Dim sPfad As String
Dim sDatei As String
Dim lErgebnisZeile As Long
Application.ScreenUpdating = False
'Zieldatei festlegen
Set oTargetSheet = ActiveWorkbook.Sheets(1)
lErgebnisZeile = 6 'Ergebnisse eintragen ab Zeile 6
'Schleife über alle Excel Dateien in einem Verzeichnis
sPfad = "\\STUDENTS_SMB\Bannenbe\Desktop\Beispielordner\"
sDatei = Dir(CStr(sPfad & "*.xl*")) 'Alle Excel Dateien
Do While sDatei  ""
'öffnen der Datei und Datenübertragung
Set oSourceBook = Workbooks.Open(sPfad & sDatei, False, True) 'nur lesend öffnen
'Datenübertragung
'Datum
Dim Suchbereich As Range
Dim Zeile As Range
Dim Spalte As Range
Dim x As Variant
Set Suchbereich = oSourceBook.Sheets(4).Range("B5:N13")
Set Suchkriterium = oTargetSheet.Sheets(1).Cells("C3")
Set Zeile = oSourceBook.Sheets(4).Range("B5:B13")
Set Spalte = oSourceBook.Sheets(4).Range("B:N4")
With Application.WorksheetFunction
oTargetSheet.Cells(lErgebnisZeile, 4).Value
x = .index(Suchbereich, .Match(Suchkriterium, Zeile, 0), .Match(Suchkriterium, Spalte, 0),1) _
_
End With
'Schritt 4: Datei wieder zu machen und nächste Schleifenrunde
oSourceBook.Close False 'nicht speichern
'Nächste Datei
sDatei = Dir()
lErgebnisZeile = lErgebnisZeile + 1 'nächste Zeile auf dem Ergebnisblatt
Loop
Application.ScreenUpdating = True 'Das Bildschirm-Aktualisieren wieder einschalten
'Variablen aufräumen
Set oTargetSheet = Nothing
Set oSourceBook = Nothing
End Sub

Es erscheint leider immer Laufzeitfehler '438'
Es wäre außerdem klasse, wenn man etwas einbauen könnte, das zunächst danach fragt, ob es diesen Namen überhaupt in dem Suchbereich gibt und ggf. direkt zur nächsten Datei übergeht.
Ich wäre sehr sehr dankbar für Hilfs- und Verbesserungsvorschläge
Viele Grüße,
Steffen

5
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: INDEX MATCH in VBA mit unterschiedlichen Dateien
31.01.2018 15:07:28
Steffen
Ich habe den Fehler bei
Set Spalte = oSourceBook.Sheets(4).Range("B:N4")
bereis bemekrt und auf "B4:N4" abgeändert. Es tut sich aber leider nichts und es wird weiterhin genanner Fehler angezeigt.
INDEX MATCH in VBA
31.01.2018 16:58:08
mmat
Hallo Steffen,
ich verstehe noch nicht so ganz, was Du da von Quelle nach Ziel übertragen willst, aber folgendes ist mir aufgefallen:
Set Suchkriterium = oTargetSheet.Sheets(1).Cells("C3")
ist problematisch. Das "Sheets(1)." in der Mitte ist überflüssig. Wichtiger ist, dass dein Suchkriterium hiermit ein Range-Objekt ist, ich halte es aber für wahrscheinlicher dass du nach einer Zahl oder nach einer Zeichenfolge suchen willst, oder nicht?
Aus dem folgenden Teil nach "With Application.WorksheetFunction" werde ich garnicht schlau:
Der Ausdruck
oTargetSheet.Cells(lErgebnisZeile, 4).Value
steht irgendwie im luftleeren Raum, er wird weder dazu verwendet um den Wert dieser Zelle zu definieren noch auszulesen. Wahrscheinlich möchtest du das Ergebnis deiner Suche hier irgendwie ablegen...
Die "Suche" kommt aber erst danach
x = .index(Suchbereich, .Match(Suchkriterium, Zeile, 0), .Match(Suchkriterium, Spalte, 0),1)
Wahrscheinlich kommt der Laufzeitfehler aus der Ecke, da ".match" mit dem Objekt nix anfangen kann. Was genau damit beabsichtigt wird, erschließt sich mir jedoch nicht. Der Wert x, dem das Ergebnis zugewiesen werden soll, wird jedenfalls nie weiterverwendet
vg, MM
Anzeige
AW: INDEX MATCH in VBA
02.02.2018 14:03:09
Steffen
Hallo mmat,
vielen Dank für Deine Antwort.
Was ich will: Ich möchte gerne die Werte, die ich mit dem Index-Befehl suche, in meine aktuelle Arbeitsmappe bekommen. Ich suche dabei nach Suchkriterien (Namen, Daten und Zahlen) , die in der aktuellen Arbeitsmappe stehen.
Das war natürlich schlecht von mir beschrieben und aus den Codes nicht herauszulesen.
Hier eine andere Variante, die ebenfalls nicht funktioniert:

Sub Zusammenfuehren()
Dim oTargetSheet As Object
Dim oSourceBook As Object
Dim sPfad As String
Dim sDatei As String
Dim lErgebnisZeile As Long
Application.ScreenUpdating = False
'Zieldatei festlegen
Set oTargetSheet = ActiveWorkbook.Sheets(1)
lErgebnisZeile = 6 'Ergebnisse eintragen ab Zeile 6
'Schleife über alle Excel Dateien in einem Verzeichnis
sPfad = "\\STUDENTS_SMB\Bannenbe\Desktop\Beispielordner\"
sDatei = Dir(CStr(sPfad & "*.xl*")) 'Alle Excel Dateien
Do While sDatei  ""
'öffnen der Datei und Datenübertragung
Set oSourceBook = Workbooks.Open(sPfad & sDatei, False, True) 'nur lesend öffnen
'Datenübertragung
'Datum
Dim Suchbereich As Range
Dim Zeile As Range
Dim Spalte As Range
Dim x As Variant
Set Suchbereich = oSourceBook.Sheets(4).Range("B5:N13")
SuchkriteriumZeile = oTargetSheet.Cells("C3")
SuchkriteriumSpalte = oTargetSheet.Cells("D5")
Set Zeile = oSourceBook.Sheets(4).Range("B5:B13")
Set Spalte = oSourceBook.Sheets(4).Range("B4:N4")
With Application.WorksheetFunction
oTargetSheet.Cells(lErgebnisZeile, 4).x = .index(Suchbereich, .Match(SuchkriteriumZeile,  _
Zeile, 0), .Match(SuchkriteriumSpalte, Spalte, 0), 1)
End With
'Schritt 4: Datei wieder zu machen und nächste Schleifenrunde
oSourceBook.Close False 'nicht speichern
'Nächste Datei
sDatei = Dir()
lErgebnisZeile = lErgebnisZeile + 1 'nächste Zeile auf dem Ergebnisblatt
Loop
Application.ScreenUpdating = True 'Das Bildschirm-Aktualisieren wieder einschalten
'Variablen aufräumen
Set oTargetSheet = Nothing
Set oSourceBook = Nothing
End Sub

Nach der ausführung bekomme ich den Laufzeitfehler 5: Ungültiger Prozeduraufruf oder ungültiges Argument.
Wie kann ich das x denn "sichtbar" machen? So wie ich es grad versucht habe?
Viele Grüße,
Steffen
Anzeige
AW: INDEX MATCH in VBA
02.02.2018 14:24:47
Steffen
Habe den "Unbeantwortet" Haken vergessen

327 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige