Microsoft Excel

Herbers Excel/VBA-Archiv

INDEX MATCH in VBA über mehrere Dateien


Betrifft: INDEX MATCH in VBA über mehrere Dateien von: Steffen
Geschrieben am: 05.02.2018 12:01:06

Hallo,
ich habe dieses Tabellenblatt: https://www.herber.de/bbs/user/119497.jpg
Unter Broker sind natürlich Namen verborgen, bei Last Update handelt es sich um Daten, bei dem Rest um Zahlen. Ich möchte in die jeweiligen Zellen untereinander die Ergebnisse aus verschiedenen Dateien einpflegen, die immer jeweils im 4. Tabellenblatt der jeweiligen Arbeitsmappe zu finden sind.
Ich habe durch versuchen und lesen verschiedenster Seiten bereits diesen Code erstellt. Leider funktioniert dieser nicht, so wie ich es oben beschrieben habe. Es werden immer Fehlermeldungen angezeigt.

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
Ich gehe davon aus, dass der Fehler ab 'Datum liegt.
Es wäre außerdem genial, wenn vor der Index-"Abfrage" nach einem Wert/Namen überprüft wird, ob die betreffende Zelle überhaupt ausgefüllt ist oder nicht. Da nicht in jedem Arbeitsblatt zu jeder Suche Werte/Namen eingetragen sind. Ich hoffe, dass mein Problem klar wurde.

Vielen Dank im Voraus,
Steffen

  

Betrifft: AW: INDEX MATCH in VBA über mehrere Dateien von: Rudi Maintaire
Geschrieben am: 05.02.2018 14:05:36

Hallo,
oTargetSheet.Cells(lErgebnisZeile, 4).x =
was soll das .x???

Gruß
Rudi


  

Betrifft: AW: INDEX MATCH in VBA über mehrere Dateien von: Steffen
Geschrieben am: 05.02.2018 14:36:37

Ich wollte mir das Ergebnis für x eben in der Zeile angeben lassen.

  With Application.WorksheetFunction
    oTargetSheet.Cells(lErgebnisZeile, 4) = .index(Suchbereich, .Match(SuchkriteriumZeile,  _
Zeile, 0), .Match(SuchkriteriumSpalte, Spalte, 0), 1)
    
    End With

Gebe ich es so ein, erscheint ebenfalls der Laufzeitfehler 5.
Was ist sonst noch falsch?


  

Betrifft: AW: INDEX MATCH in VBA über mehrere Dateien von: Steffen
Geschrieben am: 05.02.2018 15:29:49

Ich hatte ebenfalls ein Argument zu viel. Aber auch mit diesem Code funktioniert es nicht:

  With Application.WorksheetFunction
    oTargetSheet.Cells(lErgebnisZeile, 4) = .index(Suchbereich, .Match(SuchkriteriumZeile,  _
Zeile, 0), .Match(SuchkriteriumSpalte, Spalte, 0))
    
    End With



  

Betrifft: AW: INDEX MATCH in VBA über mehrere Dateien von: Rudi Maintaire
Geschrieben am: 05.02.2018 17:04:17

Hallo,
versuch mal:

Sub Zusammenfuehren()
  Dim oTargetSheet As Worksheet
  Dim oSourceBook As Workbook
  Dim oSourceSheet As Worksheet
  Dim sPfad As String
  Dim sDatei As String
  Dim lErgebnisZeile As Long
  Dim Suchbereich As Range
  Dim rngZeile As Range
  Dim Spalte As Range
  Dim vntZeile, vntSpalte
  Dim SuchKriteriumZeile
  Dim SuchKriteriumSpalte
  Dim x As Variant
  
  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
    Set oSourceSheet = oSourceBook.Sheets(4)
    
    'Datenübertragung
    'Datum
    Set Suchbereich = oSourceSheet.Range("B5:N13")
    Set Zeile = oSourceSheet.Range("B5:B13")
    Set Spalte = oSourceSheet.Range("B4:N4")
    SuchKriteriumZeile = oTargetSheet.Cells("C3")
    SuchKriteriumSpalte = oTargetSheet.Cells("D5")
    
    vntZeile = Application.Match(SuchKriteriumZeile, Zeile, 0)
    vntSpalte = Application.Match(SuchKriteriumSpalte, Spalte, 0)
    If Not IsError(vntZeile) And Not IsError(vntSpalte) Then
      oTargetSheet.Cells(lErgebnisZeile, 4) = Suchbereich.Cells(vntZeile, vntSpalte)
      lErgebnisZeile = lErgebnisZeile + 1 'nächste Zeile auf dem Ergebnisblatt
    End If
    'Schritt 4: Datei wieder schließen und nächste Schleifenrunde
    oSourceBook.Close False 'nicht speichern
    
    'Nächste Datei
    sDatei = Dir()
    lErgebnisZeile = lErgebnisZeile + 1 'nächste Zeile auf dem Ergebnisblatt
  Loop
  
  'Variablen aufräumen
  Set oTargetSheet = Nothing
  Set oSourceSheet = Nothing
  Set oSourceBook = Nothing
End Sub

Gruß
Rudi


  

Betrifft: AW: INDEX MATCH in VBA über mehrere Dateien von: Steffen
Geschrieben am: 05.02.2018 19:17:18

Hallo Rudi,
vielen Dank für Deine Mühe. Aber leider erscheint der Laufzeitfehler 5.
Hast Du den Index-Befehl einfach umgebaut oder, bitte verzeih, falls ich falsch liege, nicht berücksichtigt?
Liebe Grüße
Steffen


  

Betrifft: AW: INDEX MATCH in VBA über mehrere Dateien von: Rudi Maintaire
Geschrieben am: 06.02.2018 12:14:48

Hallo,
wo erscheint der Fehler?
Index habe ich 'umgebaut'.

Gruß
Rudi


Beiträge aus dem Excel-Forum zum Thema "INDEX MATCH in VBA über mehrere Dateien"