Vergleich von zwei Dateien - Änderungen sollen markiert werden
31.10.2019 17:42:19
zwei
ich versuche mich das erste Mal an einem Makro, was ich nicht aufgezeichnet habe ;-)
Ich habe schon viele hilfreiche Links hier gefunden, doch nun komme ich nicht weiter.
Ich habe mich bei der Erstellung des Makros an dem Beitrag von Michael (migre) vom 21.01.2016 orientiert.
Hier also mein Vorhaben:
Ich habe zwei Datein und möchte prüfen, was sich zum Vormonat geändert hat.
1. Bei alle Kinder, die Neu hinzugekommen sind, muss die Zeile (Spalte A-J) rot markiert werden (Alle Kinder, die in dem beispiel KindNeu heißen)
2. Alle Änderungen bei einem bestehenden Kind müssen rot markiert werden (die jeweilige Zelle)- Beispiel Kind mit der ID 375 kommt ab September nur noch TZ in den Hort.
3. Kinder, die nicht mehr kommen (Kind ID 433), müssen in der Excelliste ganz unten separat oder in einem neuen Tab aufgelistet werden (das habe ich noch nicht gemacht in meinem Makro, aber wenn du mir hier helfen würdest wäre das prima, da ich noch nicht weiß wie ich das mache!
Mein Problem:
A) Ich habe ein Makro geschrieben, aber es funktioniert nicht. Ich bekomme immer die Fehlermeldung: Fehler beim Kompilieren (Syntaxfehler)
B) Zuerst hatte ich die Fehlermeldung: Laufzeitfehler 9 Index außerhalb des gültigen Bereichs. den habe ich durch die Einstellung unter Datei-Optionen... geändert. wenn du hier eine Anweisung kennst, die ich in das makro mit einbauen kann, dann, verrate sie mir doch bitte und auch an welche stelle sie gehört.
C) Punkt 3 von meinem Vorhaben haben ich noch nicht im Makro berücksichtigt. Wie kann ich das am Besten machen?
D) Bestimmt gibt es noch andere Fehler, daher wäre ich sehr froh, wenn du mich darauf aufmerksam machen würdest und mir sagen, könntest, wie es richtig geht.
So sieht die Exceldatei Hort aus, d.h. der aktuelle Monat:
A B C D E F G H I J
5 ID Name Vorname Geb.Datum Beginn Änder.ab PLZ Wohnort Straße Nr. GT TZ
6 375 NameA Anna 18.02.2011 01.09.2017 12345 SchönDa Strasse 1 0 1
7 475 KindNeu1 Berta 01.01.2010 01.07.2016 12345 SchönDa Strasse 2 1 0
8 425 KindNeu2 Claus 02.01.2012 01.07.2019 12345 SchönDa Strasse 3 0 1
9 424 KindNeu3 Doris 02.08.2010 01.07.2016 12345 SchönDa Strasse 4 1 0
10 376 NameB Frederik 26.06.2012 12.08.2019 12345 SchönDa Strass 5 1 0
So sieht die Exceldatei HortVormonat, d.h. der Vormonat aus:
A B C D E F G H I J
5 ID Name Vorname Geb.Datum Beginn Änder.ab PLZ Wohnort Straße Nr. GT TZ
6 375 NameA Anna 18.02.2011 01.09.2017 12345 SchönDa Strasse 1 1 0
7 376 NameB Frederik 26.06.2012 12.08.2019 12345 SchönDa Strass 5 1 0
8 433 Altes Kind 23.04.2009 01.01.2006 31.08.2019 12345 SchönDa Verzogen 1 1 0
Mein Makro sieht so aus:
Sub idVergleich_Hort()
Dim wsHortVormonat As Worksheet
Dim wsHort As Worksheet
Dim rngDatenHortVormonat As Range
Dim rngDatenHort As Range
Dim rngTreffer As Range
Dim i As Integer
'Bildschirmaktualisierung und Zellberechnung einschränken
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With ThisWorkbook
Set wsHortVormonat = .Worksheets("HortALT")
Set wsHort = .Worksheets("Hort")
End With
'Wo stehen die alten Daten? Anpassen!
With wsHortVormonat
Set rngDatenHortVormonat = .Range("A5:L" & .Cells(.Rows.Count, 1).End(xlUp).Row)
End With
'Wo stehen die neuen Daten? Anpassen!
With wsHort
Set rngDatenHort = .Range("A5:L" & .Cells(.Rows.Count, 1).End(xlUp).Row)
End With
'Alte Daten nach ID sortieren (aufsteigend)
With wsHortVormonat.Sort
.SortFields.Clear
.SortFields.Add key:=wsHortVormonat.Range("A5"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
.SetRange rngDatenHortVormonat
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'Such-Matrix in alten Daten für SVERWEIS anpassen
With rngDatenHortVormonat
Set rngDatenHortVormonat = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count)
End With
'Neue Daten nach ID sortieren (aufsteigend)
With wsHort.Sort
.SortFields.Clear
.SortFields.Add key:=wsHort.Range("A5"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
.SetRange rngDatenHort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'Hilfspalte 1 Neu: Prüfung ob ID in alten Daten vorhanden
rngDatenHort.Offset(1, rngDatenHort.Columns.Count).Resize(rngDatenHort.Rows.Count - 1, 1). _
_
FormulaLocal = _
"=ZÄHLENWENN(" & wsHortVormonat.Name & "!" & rngDatenHortVormonat.Address & ";A6)"
'ACHTUNG, da bei der Ausführung der Makros die 1 Spalte -Anzahl der Kinder später eingefügt _
wird, stehen hier im Vergleich die finalen Spaltenin der Beschreibung mit der später eingefü _
gten Spalte A
'Hilfspalte 2 Neu: Prüfung ob Daten in Hort!C:C = HortVormonat!C:C - hier also Spalte B
rngDatenHort.Offset(1, rngDatenHort.Columns.Count + 1).Resize(rngDatenHort.Rows.Count - 1, _
_
1).FormulaLocal =
"=WENN($M60;B6=SVERWEIS(A6;" & wsHortVormonat.Name & "!" & rngDatenHortVormonat. _
Address & ";2;WAHR);"""")"
'Hilfspalte 3 Neu: Prüfung ob Daten in Neu!D:D = Alt!D:D - hier also Spalte C
rngDatenHort.Offset(1, rngDatenHort.Columns.Count + 2).Resize(rngDatenHort.Rows.Count - 1, _
_
1).FormulaLocal = _
"=WENN($M60;C6=SVERWEIS(B6;" & wsHortVormonat.Name & "!" & rngDatenHortVormonat. _
Address & ";3;WAHR);"""")"
'Hilfspalte 4 Neu: Prüfung ob Daten in Neu!E:E = Alt!E:E - hier also Spalte D
rngDatenHort.Offset(1, rngDatenHort.Columns.Count + 3).Resize(rngDatenHort.Rows.Count - 1, _
_
1).FormulaLocal = _
"=WENN($M60;D6=SVERWEIS(B6;" & wsHortVormonat.Name & "!" & rngDatenHortVormonat. _
Address & ";4;WAHR);"""")"
'Hilfspalte 5 Neu: Prüfung ob Daten in Neu!F:F = Alt!F:F- hier also Spalte E
rngDatenHort.Offset(1, rngDatenHort.Columns.Count + 4).Resize(rngDatenHort.Rows.Count - 1, _
_
1).FormulaLocal = _
"=WENN($N60;E6=SVERWEIS(B6;" & wsHortVormonat.Name & "!" & rngDatenHortVormonat. _
Address & ";5;WAHR);"""")"
'Hilfspalte 6 Neu: Prüfung ob Daten in Neu!G:G = Alt!G:G - hier also Spalte F
rngDatenHort.Offset(1, rngDatenHort.Columns.Count + 5).Resize(rngDatenHort.Rows.Count - 1, _
_
1).FormulaLocal = _
"=WENN($M60;F6=SVERWEIS(B6;" & wsHortVormonat.Name & "!" & rngDatenHortVormonat. _
Address & ";6;WAHR);"""")"
'Hilfspalte 7 Neu: Prüfung ob Daten in Neu!H:H = Alt!H:H - hier also Spalte G
rngDatenHort.Offset(1, rngDatenHort.Columns.Count + 6).Resize(rngDatenHort.Rows.Count - 1, _
_
1).FormulaLocal = _
"=WENN($M60;G6=SVERWEIS(B6;" & wsHortVormonat.Name & "!" & rngDatenHortVormonat. _
Address & ";7;WAHR);"""")"
'Hilfspalte 8 Neu: Prüfung ob Daten in Neu!I:I = Alt!I:I - hier also Spalte H
rngDatenHort.Offset(1, rngDatenHort.Columns.Count + 7).Resize(rngDatenHort.Rows.Count - 1, _
_
1).FormulaLocal = _
"=WENN($M60;H6=SVERWEIS(B6;" & wsHortVormonat.Name & "!" & rngDatenHortVormonat. _
Address & ";8;WAHR);"""")"
'Hilfspalte 9 Neu: Prüfung ob Daten in Neu!J:J = Alt!J:J - hier also Spalte I
rngDatenHort.Offset(1, rngDatenHort.Columns.Count + 8).Resize(rngDatenHort.Rows.Count - 1, _
_
1).FormulaLocal = _
"=WENN($M60;I6=SVERWEIS(B6;" & wsHortVormonat.Name & "!" & rngDatenHortVormonat. _
Address & ";9;WAHR);"""")"
'Hilfspalte 10 Neu: Prüfung ob Daten in Neu!K:K = Alt!K:K - hier also Spalte J
rngDatenHort.Offset(1, rngDatenHort.Columns.Count + 9).Resize(rngDatenHort.Rows.Count - 1, _
_
1).FormulaLocal = _
"=WENN($M60;J6=SVERWEIS(B6;" & wsHortVormonat.Name & "!" & rngDatenHortVormonat. _
Address & ";10;WAHR);"""")"
'Hilfspalte 11 Neu: Prüfung ob Daten in Neu!L:L = Alt!L:L- hier also Spalte K
rngDatenHort.Offset(1, rngDatenHort.Columns.Count + 10).Resize(rngDatenHort.Rows.Count - 1, _
_
1).FormulaLocal = _
"=WENN($M60;K6=SVERWEIS(B6;" & wsHortVormonat.Name & "!" & rngDatenHortVormonat. _
Address & ";11;WAHR);"""")"
'Datenbereich um Hilfsspalten erweitern
With rngDatenHort
Set rngDatenHort = .Resize(.Rows.Count, .Columns.Count + 12)
End With
'Fehler abfangen wenn keine Daten zutreffen
On Error Resume Next
'Autofilter in neuen Daten: Wenn Hilfsspalte 1 = 0, dann dieser Datensatz rot
wsHort.AutoFilterMode = False
With rngDatenHort
.AutoFilter field:=13, Criteria1:="0"
For Each rngTreffer In .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count). _
SpecialCells(xlCellTypeVisible)
If rngTreffer.Column Ich wäre euch für Hilfe wirklich sehr dankbar. Ich habe den ganzen Feiertag gerade am Rechner gesessen während meine Familie einen schönen Ausflug gemacht hat und es klappt trotzdem nicht - echt frustrierend!
Vielen Dank!
Anja