Results 1 to 4 of 4

Thread: INDEX MATCH in VBA over different data files

  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


    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,

  2. #2
    VBAX Expert
    Mar 2008
    Kent, England
    Maybe try:

    Set Suchbereich = oSourceBook.Sheets(4).Range("B4:N13")
    SuchkriteriumZeile = oTargetSheet.Cells("C3")
    SuchkriteriumSpalte = oTargetSheet.Cells("E5")
    Set Zeile = oSourceBook.Sheets(4).Range("B4:B13")
    Set Spalte = oSourceBook.Sheets(4).Range("B4:N4")
    I was not told it was impossible, so i did it.

  3. #3
    Unfortunately, this could not fix the problem.
    Clicking "Debugging" after the Runtime error occurs, highlights the line "SuchkriteriumZeile = oTargetSheet.Cells("C3")"
    Do you know why?

  4. #4
    VBAX Guru mancubus's Avatar
    Dec 2010
    Ride the wind to the sun
    PHP Code:
    Range(Range("A2"), Range("E10"))
    Range(Cells(21), Cells(105)) 
    are all identical

    so Range("C3") or Cells(3, 3) shoud be used to refer to the cell at the intersection point of row 3 and column 3

    1) Posting Code
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

Posting Permissions

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