Consulting

Results 1 to 4 of 4

Thread: INDEX MATCH in VBA over different data files

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1

    INDEX MATCH in VBA over different data files

    Hello, as I am new to this forum, I hope you will excuse me, not conveying my problem properly.
    Anyways, I am looking for a way to match information via the Index and Match command in VBA. The Worksheet, that I want the information to go looks
    Broker ABC
    Broker/Analyst Last Update Revenue EBITDA EBIT EPS reported (in EUR)
    ABC 24.07.2015 568
    With the command in E5 reading this (which is "568"): =INDEX('\\STUDENTS_SMB\Bannenbe\Desktop\Beispielordner\[Datenmappe.xlsx]BspBlatt'!$B$5:$N$13;VERGLEICH($C$3;'\\STUDENTS_SMB\Bannenbe\Desktop\Beispi elordner\[Datenmappe.xlsx]BspBlatt'!$B$5:$B$13;0);VERGLEICH(E$5;'\\STUDENTS_SMB\Bannenbe\Desktop\Beis pielordner\[Datenmappe.xlsx]BspBlatt'!$B$4:$N$4;0))

    Broker refers to names, Last Updates to dates and the rest to numbers. The command in the standard excel surface works fine.
    I do have a lot of data files where the information is always in the fourth sheet. And so far I have created this code:

    Sub ZusammenfuehrenHerber()
    Dim oTargetSheet As Object
       Dim oSourceBook As Object
       Dim sPfad As String
       Dim sDatei As String
       Dim lErgebnisZeile As Variant
    Application.ScreenUpdating = False
    'Target Data File
        Set oTargetSheet = ActiveWorkbook.Sheets(1)
        lErgebnisZeile = 6 'Ergebnisse eintragen ab Zeile 6
    'Loop over all data files 
         sPfad = "\\STUDENTS_SMB\Bannenbe\Desktop\Beispielordner\"
         sDatei = Dir(CStr(sPfad & "*.xl*")) 'Alle Excel Dateien
    Do While sDatei <> ""
        'open data file
        Set oSourceBook = Workbooks.Open(sPfad & sDatei, False, True) 'nur lesend öffnen
        'data transfer (just to see whether I am correct up to here)
        oTargetSheet.Cells(lErgebnisZeile + 7, 3).Value = _
        oSourceBook.Sheets(4).Cells(4, 5).Value
        'Data transfer for "Revenue" (Just this as example)
        Dim Suchbereich As Range
        Dim SuchkriteriumZeile As Range
        Dim SuchkriteriumSpalte As Range
        Dim Zeile As Range
        Dim Spalte As Range
        Set Suchbereich = oSourceBook.Sheets(4).Range("B5:N13")
        SuchkriteriumZeile = oTargetSheet.Cells("C3")
        SuchkriteriumSpalte = oTargetSheet.Cells("E5")
        Set Zeile = oSourceBook.Sheets(4).Range("B5:B13")
        Set Spalte = oSourceBook.Sheets(4).Range("B4:N4")
        With Application.WorksheetFunction
            oTargetSheet.Cells(lErgebnisZeile, 5) = .index(Suchbereich, .Match(SuchkriteriumZeile, Zeile, 0), .Match(SuchkriteriumSpalte, Spalte, 0))
        End With
        'close data file 
        oSourceBook.Close False 'nicht speichern
        'next data file
        sDatei = Dir()
        lErgebnisZeile = lErgebnisZeile + 1 'nächste Zeile auf dem Ergebnisblatt
    Loop
    Application.ScreenUpdating = True 'Das Bildschirm-Aktualisieren wieder einschalten
    Set oTargetSheet = Nothing
         Set oSourceBook = Nothing
    End Sub
    Unfortunately, this code does not work. I guess the mistake must be following 'Data transfer for "Revenue", because the value, that I want to have from the Source before is delivered properly.
    Hope, you can help. Thanks in advance,
    Steffen
    Last edited by Aussiebear; 04-24-2023 at 04:55 AM. Reason: Adjusted the code tags

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •