Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

VBA mit SVERWEIS und LOOP

Betrifft: VBA mit SVERWEIS und LOOP von: Bastian
Geschrieben am: 20.10.2020 07:40:18

Hallo zusammen,

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
Ich habe bereits versucht einen Loop zu erstellen, sodass zuerst alle vorhandenen Werte für Spalte E bearbeitet werden und dann alle Werte für F etc. bis auch die letzte Spalte (ZZ oder mehr) mit Werten bearbeitet ist, bekomme es aber nicht hin.

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
Es auch für die Bestimmung der letzte Spalte Gibt (würde dann vermutlich so aussehen)
   For lCol = 5 To Cells(3, Columns.Count).End(xlToLeft).Column
Aber ich bekomme es einfach nicht unter einen Hut...

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

Betrifft: AW: VBA mit SVERWEIS und LOOP
von: ChrisL
Geschrieben am: 20.10.2020 12:54:20

Hi Bastian

Grundsätzlich würde ich es mittels Ordnerabfrage und Power-Query versuchen. Es wäre allerdings zu prüfen, ob/wie sich die Transformation der Daten in die gewünschte Struktur erreichen lässt.

Im aktuellen Ansatz würde ich die WENN-Abfrage direkt in die Formel reinpacken.
=WENN(E$2="";SVERWEIS(E$1&"*";[Beispieldatei.xls]BatchReport!$A$1:$I$9999;7;0);SVERWEIS(E$2&"*"; [Beispieldatei.xls]BatchReport!$B$1:$I$9999;6;0))

Damit kannst du die Formel dann gleich für einen ganzen Bereich einsetzen z.B.
Range(Cells(lRow, 5), Cells(lRow, 8)).Formula = "=IF(E$2="""",VLOOKUP(E$1&""*"",[Beispieldatei.xls]BatchReport!$A$1:$I$9999,7,0),VLOOKUP(E$2&""*"",[Beispieldatei.xls]BatchReport!$B$1:$I$9999,6,0))"

cu
Chris

Betrifft: AW: VBA mit SVERWEIS und LOOP
von: Bastian
Geschrieben am: 21.10.2020 01:19:10

Hallo Chris,

Power query sagt mir pauschal erstmal nichts, aber ich mache mich mal schlau, danke für den Tip.

Die wenn Abfrage habe ich bereits versucht, das macht das ganze aber nur unnötig langsam, es ist wesentlich effizienter wenn ich die Auswahl welche Zellen bzw. welche Matrix ich abfragen will bereits im code treffe.

Ich habe heute leider wenig Zeit aber deinen zweiten Ansatz werde ich auf jeden Fall mal ausprobieren, DANKE
Rückmeldung folgt

Gruß
Bastian

Betrifft: AW: VBA mit SVERWEIS und LOOP
von: ChrisL
Geschrieben am: 21.10.2020 08:02:55

Hi Bastian

Zu PQ z.B.: https://www.youtube.com/watch?v=NY3m_nLGuTg

Ob die WENN-Abfrage im Code oder in der Formel ist, wird keinen spürbaren Unterschied machen und Ansatz 2 funktioniert leider nicht ohne Ansatz 1.

cu
Chris

Betrifft: SVERWEIS Matrix in Abhängigkeit
von: Bastian
Geschrieben am: 23.10.2020 07:08:35

Hallo Tim,

PQ habe ich mir mal ein wenig angeschaut und glaube, dass es für diesen Fall nur bedingt einzusetzen wäre, bzw. bin mit der VBA Lösung bisher eigentlich ganz zufrieden. Ich werde es aber auf jeden Fall im Hinterkopf behalten und für andere Zwecke einsetzen, nochmal Danke für den Tip!

Die Wenn Verschachtelung habe ich eingebaut, vllt war meine Wahrnehmung hier etwas subjektiv, ja. Ich habe es auch hinbekommen, dass er jetzt automatisch die letzte Spalte bestimmt und die Formeln bis zu dieser reinschreibt. Allerdings glaube ich, dass es durchaus eleganter gegangen wäre. Hier mal der code
    LCol1 = Sheets(1).Cells(2, 16384).End(xlToLeft).Column
    LCol2 = Sheets(1).Cells(3, 16384).End(xlToLeft).Column

    If LCol1 < LCol2 Then
    LCol = LCol2
    Else: LCol = LCol1
    End If
......

        Range(Cells(LRow, 7), Cells(LRow, LCol)).Formula = _
        "=IF(G$3="""",VLOOKUP(G$2 & ""*"",'" & sPath & _
        "[" & sFile & "]" & sWks & "'!$A1:I9999,7,0), VLOOKUP(G$3 & ""*"",'" & _
        sPath & "[" & sFile & "]" & sWks & "'!$B1:I9999,6,0))"
    
......

https://www.herber.de/bbs/user/141032.xlsm

Wie würdet ihr die Bestimmung der letzten Spalte lösen bzw. einbauen?

So wie das Programm aktuell ist kann ich es einsetzen, soweit alles gut. Allerdings habe ich mir überlegt es noch ein wenig genauer/anwenderfreundlicher zu machen, leider aber keine Idee wie dies umzusetzen wäre.

Die SVERWEIS Abfrage liefert mir den ersten Wert zum Suchkriterium, leider kann es schonmal vorkommen, dass es aber mehrere treffer gibt. Dies könnte ich eingrenzen wenn ich die Matrix der zweiten Suche (Suchkriterium aus Zeile 2, Suche in Spalte B) in Abhängikeit von dem Ergebnis aus der ersten Suche (Zeile1, Spalte A) mache. Konkret wäre es dann so:

Steht ein Wert in Zeile 1 suche diesen Wert in Spalte A und Bestimme die Position, zum Beispiel Zeile 346. Dann suche den Wert aus aus Zeile 2 und beginne mit der Suche in Spalte B ab Zeile 346.

Ist das Verständlich?
Bin gespannt auf euren Input

Gruß
Bastian

Betrifft: AW: SVERWEIS Matrix in Abhängigkeit
von: ChrisL
Geschrieben am: 23.10.2020 08:10:58

Hi Bastian

Wie würdet ihr die Bestimmung der letzten Spalte lösen bzw. einbauen?

z.B. mit MAX-Formel:
With Worksheets(1)
    lCol = WorksheetFunction.Max(.Cells(2, Columns.Count).End(xlToLeft).Column, _
            .Cells(3, Columns.Count).End(xlToLeft).Column)
End With

So wie das Programm aktuell ist kann ich es einsetzen, soweit alles gut. Allerdings habe ich mir überlegt es noch ein wenig genauer/anwenderfreundlicher zu machen, leider aber keine Idee wie dies umzusetzen wäre.

Ich würde dir empfehlen, zuerst dein Brainstroming abzuschliessen und danach mit der Umsetzung zu beginnen ;)
Den Fokus würde ich auf eine 'saubere' Datenstruktur legen. Soweit ich dies aus der Distanz beurteilen kannst, mischst du unterschiedliche Informationen (A Hauptkategorie, B Unterkategorie) in der gleichen Spalte, was der Ursprung der Komplexität sein könnte.


"SVERWEIS mit mehreren Suchkriterien" kannst du in deinem Fall (Resultat = Zahl) vermutlich mit SUMMEWENNS() lösen.

cu
Chris

Betrifft: AW: SVERWEIS Matrix in Abhängigkeit
von: Bastian
Geschrieben am: 23.10.2020 08:59:50

Hallo Chris,

vielen Dank für deine Antwort ist eingebaut und funktioniert!

Prinzipiell gebe ich dir Recht, allerdings habe ich auf die Datenstruktur keinen Einfluss, die Daten sind leider wie sie sind.

Es geht um Zeiten, diese stehen immer in Spalte G. Das Suchkriterium kann in A (Mashine1, Maschine2, etc.) oder B (Schritt1, Schritt2, etc.) stehen. Das Problem ist nun, dass mehrere Maschinen denselben Schritt haben können, deshalb wäre es schön gewesen zu sagen Suche zuerst nach Maschine 1 und dann ab da nach Schritt1, so wüsste ich sicher, dass die ausgelesene Zeit auch wirklich zu der gesuchten Maschine gehört.

SUMMEWENNS kann glaube ich nicht aus geschlossenen Dateien lesen, das ist leider Grundvorraussetzung, deswegen ja der SVERWEIS.

Gruß

Betrifft: AW: SVERWEIS Matrix in Abhängigkeit
von: ChrisL
Geschrieben am: 23.10.2020 09:32:04

Hi

Ja stimmt, SUMMEWENNS und geschlossene Mappe habe ich übersehen. Aber ich glaube da gibt es keine Alternative, ausser alle Zellen einzeln mit Verweis abzusuchen, was aus Gründen der Performance nicht in Frage kommt oder die Datei eben zu öffnen.

cu
Chris

Betrifft: Geschlossen
von: Bastian
Geschrieben am: 25.10.2020 07:39:49

Hallo Chris,

ich habe zu dem Thema auslesen über VBA nochmal einen Thread gestartet, ich hoffe du fühlst dich dadurch nicht auf den Schlips getreten, ich glaube ich habe mich einfach nicht richtig ausgedrückt. Wenn bei dem neuen Thema wieder nichts raus kommt lass ichs bleiben.
Danke für deine Unterstützung!

Beiträge aus dem Excel-Forum zum Thema "VBA mit SVERWEIS und LOOP"