Office: Vergleich von zwei Spalten und ergänzen mittels VBA

Hallo Leute,
ich hoffe, dass einer von euch mir helfen kann.
Ich habe zwei Dateien (Dispo und Silo) und die einzige Zeile die hier für mich wichtig ist wäre die Lieferschein-Nr. in Spalte A. Jetzt möchte ich es gerne schaffen, dass die Lieferschein-Nr. aus Dispo mit der aus Silo verglichen wird und fehlende Einträge ergänzt werden.

Bsp.

Datei Dispo
Lieferschein-Nr.
1111
2222
3333
4444

Datei Silo
Lieferschein-Nr.
1111
2222
2223
3333
3334
4444

Datei Dispo nach Makro durchlauf
1111
2222
2223
3333
3334
4444

Hab mich gefühlt schon durch alle Foren gesucht aber bekomme es einfach nicht hin. Vielleicht hat einer von euch eine gute Idee wie ich das schaffen kann?
Danke

 

Hi Maximilian,

Code:

Sub Vergleichen()
   Dim lngZeile As Long
   Dim lngZiel As Long
   With Workbooks("Silo").Worksheets("Tabelle1")
      For lngZeile = 1 To 7
         If IsError(Application.Match(.Cells(lngZeile, 1), _
            Workbooks("Dispo").Worksheets("Tabelle1").Columns(1), 0)) Then
            lngZiel = Application.Match(.Cells(lngZeile, 1), _
               Workbooks("Dispo").Worksheets("Tabelle1").Columns(1), 1)
            Workbooks("Dispo").Worksheets("Tabelle1").Rows(lngZiel + 1).Insert
            Workbooks("Dispo").Worksheets("Tabelle1").Cells(lngZeile, 1) = .Cells(lngZeile, 1)
         End If
      Next lngZeile
   End With
End Sub

Namen der Tabellenblätter musst du entsprechend anpassen.

Falls es tatsächlich unterschiedliche Arbeitsmappen sind, dann musst du vor Worksheets("Dispo") noch den Namen der Arbeitsmappe ergänzen.

Bis später,
Karin

 

Super Vielen Dank schonmal!

Bekomme jetzt leider eine Fehlermeldung 13. Ich glaube das muss an unterschiedlichen Datentypen liegen. Sehe ich das richtig und hast du eine Idee was ich noch Falsch habe ?


Dim lngZeile As Long
Dim lngZiel As Long

Workbooks.Open Filename:="Users:test:Desktop:TestDispo.xlsm"
With Workbooks("TestSilo.xlsm").Worksheets("Blatt1")
For lngZeile = 1 To 7
If IsError(Application.Match(.Cells(lngZeile, 1), _
Workbooks("TestDispo.xlsm").Worksheets("Blatt1").Columns(1), 0)) Then
lngZiel = Application.Match(.Cells(lngZeile, 1), _
Workbooks("TestDispo.xlsm").Worksheets("Blatt1").Columns(1), 1)
Workbooks("TestDispo.xlsm").Worksheets("Blatt1").Rows(lngZiel + 1).Insert
Workbooks("TestDispo.xlsm").Worksheets("Blatt1").Cells(lngZeile, 1) = .Cells(lngZeile, 1)
End If
Next lngZeile
End With

 

Und was sagt der Debugger genau?
Ich erhalte keine Fehlermeldung in meiner Beispielmappe.

Bis später,
Karin

 

Laufzeitfehler 13

Typenkonflikt

ist die genaue Fehlermeldung

 

Wie schon geschrieben - in meiner Beispielmappe erhalte ich keinen Fehler. Da kann ich dir leider nicht weiterhelfen, da ich deine Mappen nicht kenne.
Übrigens: es gibt hier im Forum die Möglichkeit Mappen hochzuladen.

Bis später,
Karin

 

Den Anhang Abgleich.xlsm betrachten

Hab jetzt eine Beispiel Datei gemacht und auch hier tritt der Fehler leider wieder auf. Vielleicht macht es das leichter ihn zu verstehen.

Vielen Dank für die Hilfe

 

Du hast Leerzeilen im Tabellenblatt "Silo", im Gegensatz zu dem Beipsiel aus deinem Eröffnungsbeitrag - das muss berücksichtigt werden. Außerdem ist unklar, ob in "Dispo" tatsächlich schon Leerzeilen an den entsprechenden Stellen vorhanden sind oder nicht - das unterscheidet sich in deiner Mappe auch von deinem Eröffnungsbeitrag.

Du müsstest schon mal genau erklären, wie die tatsächliche Ausgangssituation ist, den davon hängt ja ab, wie der Code aussehen muss.

Bis später,
Karin

 

Nein das hab ich Falsch in der Excel eingetragen. Es gibt keine Leerzeilen. Über eine Userform schreibe ich immer in die letzte freie Zeile eine neue Lieferschein-Nr.

 

So läuft der Code in deiner Mappe korrekt - unter der Bedingung, dass es in keiner der beiden Tabellen Leerzellen in Spalte A gibt:

Code:

Private Sub CommandButton1_Click()
   Dim lngZeile As Long
   Dim lngZiel As Long
   Dim lngLetzteQuelle As Long
   Dim lngLetzteZiel As Long
   With Worksheets("Silo")
      lngLetzteQuelle = IIf(IsEmpty(.Cells(.Rows.Count, 1)), _
         .Cells(.Rows.Count, 1).End(xlUp).Row, .Rows.Count)
      For lngZeile = 2 To lngLetzteQuelle
         lngLetzteZiel = IIf(IsEmpty(Cells(Rows.Count, 1)), _
            Cells(Rows.Count, 1).End(xlUp).Row, Rows.Count)
         If IsError(Application.Match(.Cells(lngZeile, 1), _
            Worksheets("Dispo").Columns(1), 0)) Then
            If IsError(Application.Match(.Cells(lngZeile, 1), _
               Worksheets("Dispo").Columns(1), 1)) Then
               lngZiel = 2
            ElseIf Application.Match(.Cells(lngZeile, 1), _
               Worksheets("Dispo").Columns(1), 1) = lngLetzteZiel Then
               lngZiel = lngLetzteZiel + 1
            Else
               lngZiel = Application.Match(.Cells(lngZeile, 1), _
                  Worksheets("Dispo").Columns(1), 1) + 1
            End If
            Worksheets("Dispo").Rows(lngZiel).Insert
            Worksheets("Dispo").Cells(lngZiel, 1) = .Cells(lngZeile, 1)
         End If
      Next lngZeile
   End With
End Sub

Bis später,
Karin

 

Super klasse funktioniert!
Kannst du mir vielleicht noch die einzelnen Schritte erklären so dass ich verstehe was die einzelnen Befehle bedeuten?

 

Der Code baut auf der Tabellen-Formel VERGLEICH() auf - zur Verwendung der Vergleichstypen 0 und 1 schaue in der Excel-Hilfe.

Code:

Private Sub CommandButton1_Click()
   Dim lngZeile As Long
   Dim lngZiel As Long
   Dim lngLetzteQuelle As Long
   Dim lngLetzteZiel As Long
   ' bezogen auf Tabelle "Silo"
   With Worksheets("Silo")
      ' letzte belegte Zeile in Spalte A
      lngLetzteQuelle = IIf(IsEmpty(.Cells(.Rows.Count, 1)), _
         .Cells(.Rows.Count, 1).End(xlUp).Row, .Rows.Count)
      ' Schleife von 2 bis zur letzten belegten Zeile
      For lngZeile = 2 To lngLetzteQuelle
         ' letzte belegte Zeile in Tabelle "Dispo" ermitteln
         ' ist notwendig, falls neue Werte unterhalb der vorhandenen hinzugefügt werden müssen
         lngLetzteZiel = IIf(IsEmpty(Cells(Rows.Count, 1)), _
            Cells(Rows.Count, 1).End(xlUp).Row, Rows.Count)
         ' VERGLEICH() mit Vergleichstyp 0 der laufenden Zelle Tabelle "Silo" mit Spalte A Tabelle "Dispo"
         ' das löst einen Fehler aus wenn nicht gefunden
         If IsError(Application.Match(.Cells(lngZeile, 1), _
            Worksheets("Dispo").Columns(1), 0)) Then
            ' VERGLEICH() mit Vergleichstyp 1 löst Fehler aus, wenn kein Wert < als Suchwert vorhanden
            If IsError(Application.Match(.Cells(lngZeile, 1), _
               Worksheets("Dispo").Columns(1), 1)) Then
               ' Einfügezeile = 2
               lngZiel = 2
            ' VERGLEICH() mit Vergleichstyp 1 liefert als Ergebniszeile = letzte Zeile in Tabelle "Dispo"
            ' das bedeutet, der Suchwert > als der größte Wert
            ElseIf Application.Match(.Cells(lngZeile, 1), _
               Worksheets("Dispo").Columns(1), 1) = lngLetzteZiel Then
               ' Einfügezeile = letzte Zeile + 1
               lngZiel = lngLetzteZiel + 1
            ' VERGLEICH() gibt die Zeile des Wertes aus, der als nächst kleiner Wert zum Suchwert steht
            Else
               ' Einfügezeile ist die nächste zur gefundenen Zeile
               lngZiel = Application.Match(.Cells(lngZeile, 1), _
                  Worksheets("Dispo").Columns(1), 1) + 1
            End If
            ' in Zielzeile Leerzeile einfügen
            Worksheets("Dispo").Rows(lngZiel).Insert
            ' in Zielzeile den laufenden Inhalt eintragen
            Worksheets("Dispo").Cells(lngZiel, 1) = .Cells(lngZeile, 1)
         End If
      Next lngZeile
   End With
End Sub

Falls noch Fragen sind - einfach nochmal nachfragen.

Bis später,
Karin

 

Ich glaube ich hab es im groben verstanden. Eine Frage habe ich dann doch noch! Und zwar Vorher weiß VBA, dass bei der For Anweisung nicht mehr in Tabelle Silo sondern Dispo nach der letzten Zeile suchen soll ?

Und Vielen Dank für die Hilfe Karin! Super Forum hier!

 

Wenn du die Code-Zeile für die letzte belegte Zeile lngLetzteQuelle in Tabelle "Silo" genau anschaust siehst du, dass vor Cells und Range immer ein Punkt steht - damit weiß Excel, dass es sich auf das Tabellenblatt beziehen soll, welches mit der With-Anweisung angesprochen wurde, also auf "Silo". In der Code-Zeile für die letzte belegte Zeile lngLetzteZiel fehlen diese Punkte und Excel weiß deshalb, dass es sich auf die aktive Tabelle beziehen soll, was durch die Benutzung des UserForms im Tabellenblatt "Dispo" ja gegeben ist.

Bis später,
Karin

 

Klasse danke.
Hast du noch eine Idee von ich zu den Lieferschein-Nr. noch Werte aus anderen Spalten kopieren will und in unterschiedliche Spalten wieder einfügen möchte. Wie stelle ich das bei dem Code am besten an ?

Danke