VBA mit SVERWEIS und LOOP
20.10.2020 07:40:18
Bastian
das ist mein erster Beitrag in diesem Forum, obwohl ich wirklich schon seit Jahren begeisterter Mitleser bin, meistens aber für Excel Fragen, da kann ich mir dann auch mit den bereits bestehenden Themen selbst helfen.
Ich habe mich dieses mal allerdings an VBA gewagt, da ich mir mit reinen Excel Formeln bei dieser Ausgabenstellung nicht mehr weiterhelfen kann (und es glaube ich auch keinen Sinn macht).
Die Problemstellung ist folgende:
Ich habe (theoretisch) hunderte Exceldateien die alle nach einem annähernd gleichen Schema aufgebaut sind. Manche Informationen stehen in Spalte A, manche in H, etc. Die Zeilen können dabei durchaus Variieren, deswegen, und weil ich die Dateien nicht einzeln öffnen will habe ich mich für SVERWEIS entschieden um die benötigten Informationen zu bekommen und in einer Datei zu vereinigen. Soweit so gut, würde ja ohne VBA noch funktionieren.
Der zweite Schritt war dann das automatische einlesen der Dateinamen (-Pfade) und das generieren der SVERWEIS Formeln korrespondierend zu Dateiname/ -Pfad und gewünschtem Wert. Das hat mich bereits so manchen Nerv und Stunde gekostet, aber ich glaube ich habe es irgendwie hinbekommen (wohl eher hingepfuscht vermutlich).
Leider sieht meine Lösung eine eigene Schleife für jede Spalte vor und auch die Abfrage ob Zeile1 oder Zeile 2 relevant ist würde sicher viel eleganter gehen.
If Range("E2").Value = "" Then
Range("E" & lRow).Formula = _
"=VLOOKUP(E1 & ""*"",'" & sPath & _
"[" & sFile & "]" & sWks & "'!A1:I9999,7,0)"
Else
Range("E" & lRow).Formula = _
"=VLOOKUP(E2 & ""*"",'" & sPath & _
"[" & sFile & "]" & sWks & "'!B1:I9999,6,0)"
End If
If Range("F2").Value = "" Then
Range("F" & lRow).Formula = _
"=VLOOKUP(F1 & ""*"",'" & sPath & _
"[" & sFile & "]" & sWks & "'!A1:I9999,7,0)"
Else
Range("F" & lRow).Formula = _
"=VLOOKUP(F2 & ""*"",'" & sPath & _
"[" & sFile & "]" & sWks & "'!B1:I9999,6,0)"
End If
Next lRow
Mir ist bewusst dass es die Funktion die ich bereits für die bestimmung der letzte Zeile benutze
For lRow = 3 To Cells(Rows.Count, 2).End(xlUp).Row
For lCol = 5 To Cells(3, Columns.Count).End(xlToLeft).Column
Daher meine Frage, ist mein Ansatz den überhaupt für die weitere Aufgabenstellung zu gebrauchen? Wenn ja, wo müsste ich ansetzen um dahin zu kommen?
Hier die Datei zur Auswertung:
https://www.herber.de/bbs/user/140984.xls
Tausend Dank für euren Input
Hier noch die komplette Funktion:
Sub ReadDat()
Dim sPath As String, sFile As String
Dim sWks As String
Dim lRow As Long
For lRow = 3 To Cells(Rows.Count, 2).End(xlUp).Row
sPath = ThisWorkbook.Path & "\"
sFile = Range("B" & lRow).Value
sWks = "BatchReport"
If Dir(sPath & sFile) = "" Then
Beep
MsgBox "Quelldatei " & sPath & sFile & _
" wurde nicht gefunden!"
Exit Sub
End If
Range("C" & lRow).Formula = _
"=VLOOKUP(C1 & ""*"",'" & sPath & _
"[" & sFile & "]" & sWks & "'!E1:I9999,2,0)"
Range("D" & lRow).Formula = _
"=LEFT(RC[-1],SEARCH(""["",RC[-1],1)-2)"
If Range("E2").Value = "" Then
Range("E" & lRow).Formula = _
"=VLOOKUP(E1 & ""*"",'" & sPath & _
"[" & sFile & "]" & sWks & "'!A1:I9999,7,0)"
Else
Range("E" & lRow).Formula = _
"=VLOOKUP(E2 & ""*"",'" & sPath & _
"[" & sFile & "]" & sWks & "'!B1:I9999,6,0)"
End If
If Range("F2").Value = "" Then
Range("F" & lRow).Formula = _
"=VLOOKUP(F1 & ""*"",'" & sPath & _
"[" & sFile & "]" & sWks & "'!A1:I9999,7,0)"
Else
Range("F" & lRow).Formula = _
"=VLOOKUP(F2 & ""*"",'" & sPath & _
"[" & sFile & "]" & sWks & "'!B1:I9999,6,0)"
End If
Next lRow
End Sub