PDA

View Full Version : INDEX MATCH in VBA over different data files



ste.bann
02-05-2018, 08:45 AM
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

georgiboy
02-06-2018, 07:22 AM
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")

ste.bann
02-14-2018, 01:29 AM
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?

mancubus
02-14-2018, 06:12 AM
Range("A2:E10")
Range(Range("A2"), Range("E10"))
Range(Cells(2, 1), Cells(10, 5))

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