Vergleich zweier Listen - zu langsam, bitte Hilfe!

Bild

Betrifft: Vergleich zweier Listen - zu langsam, bitte Hilfe!
von: Bernhard
Geschrieben am: 14.03.2005 13:07:04
Liebe Excel - VBA Spezialisten,
ich muss zwei größere Listen miteinander vergleichen. Das Schlüsselfeld ist in beiden Listen ein zusammengesetzter Wert aus Ordernr. und Position, die ich miteinander verknüpfe.
Dann wird in einer Doppelschleife jede Zeile der Original-Liste mit allen Zeilen der zweiten Liste verglichen und bei Treffer wird die gesamte Zeile rot eingefärbt.
Beide Listen enthalten etwa 4000 Zeilen.
Das Makro scheint zu funktionieren - jedoch wenn ich über alle Zeilen suche, dann benötigt das Makro zu lange bzw. es stürzt vielleicht ab (ich habe nach 30 Minuten jeweils abgebrochen).
Was kann ich machen um die Geschwindigkeit zu optimieren oder habe ich einen ganz anderen Fehler?
Ich bitte um Hilfe!
Vielen Dank -viele Grüße
Bernhard


Sub CompareFiles()
Dim i As Single 'Schleifenzähler Zeilen
Dim k As Single
Dim l As Integer
Dim Original As Workbook
Dim CompareWith As Workbook
Dim CompareWithName As String
Dim OriginalName As String
OriginalName = ActiveWorkbook.name
Set Original = Workbooks(OriginalName)
Const dir = "c:\Makro_Test"
Application.Dialogs(xlDialogOpen).Show "c:\Makro_Test"
CompareWithName = ActiveWorkbook.name
Set CompareWith = Workbooks(CompareWithName)
Application.ScreenUpdating = False
For i = 1 To Original.Sheets(1).UsedRange.Rows.Count
    For k = 1 To CompareWith.Sheets(1).UsedRange.Rows.Count
        If Original.Sheets(1).Cells(i, 11) & Original.Sheets(1).Cells(i, 12) = CompareWith.Sheets(1).Cells(k, 11) & CompareWith.Sheets(1).Cells(k, 12) Then
            Original.Sheets(1).Rows(i).Interior.ColorIndex = 3
            Original.Sheets(1).Rows(i).Font.ColorIndex = 2
            l = 1 + l
            Exit For
        End If
    Next k
Next i
    Debug.Print l & " Übereinstimmungen"
    CompareWith.Close
Application.ScreenUpdating = True
End Sub

Bild

Betrifft: AW: Vergleich zweier Listen - zu langsam, bitte Hilfe!
von: Bert
Geschrieben am: 14.03.2005 13:18:26
Es macht keinen Sinn, Scheifenzähler As Single zu deklarieren, As Long ist da angebracht.
Bilde 2 Arrays aus den UsedRange und vergleiche diese, ist um Lichtjahre schneller,
als immer auf die Zellen zuzugreifen.
Bert
Bild

Betrifft: AW: Vergleich zweier Listen - zu langsam, bitte Hilfe!
von: Mac4
Geschrieben am: 14.03.2005 13:26:05
Hi Bert,
ich hänge mich mal an das Thema dran.
Kannst Du vielleicht mal anhand eines kleinen Beispielcodes zeigen, wie so ein Array ausehen könnte, bzw. wie es gefüllt wird?
Danke Dir schon mal im Voraus!
Marc
Bild

Betrifft: AW: Vergleich zweier Listen - zu langsam, bitte Hilfe!
von: Bernhard
Geschrieben am: 14.03.2005 14:36:31
Hallo Bert,
leider habe ich noch nicht mit Arrays gearbeitet. Über ein kleines Beispiel bzw. Link auf eine Hilfe freue ich mich.
Danke!
Viele Grüße
Bernhard
Bild

Betrifft: AW: Vergleich zweier Listen - zu langsam, bitte Hilfe!
von: Holger Levetzow
Geschrieben am: 14.03.2005 13:22:59
Hallo Bernhard,
versuche mal, die Variablen nicht jedesmal zusammenzuführen/berechnen ..., z.B.
bis_i=Original.Sheets(1).UsedRange.Rows.Count
bis_k=CompareWith.Sheets(1).UsedRange.Rows.Count
For i = 1 To bis_i
wert=Original.Sheets(1).Cells(i, 11) & Original.Sheets(1).Cells(i, 12)
For k = 1 To bis_k
If wert = CompareWith.Sheets(1).Cells(k, 11) & CompareWith.Sheets(1).Cells(k, 12) Then
Original.Sheets(1).Rows(i).Interior.ColorIndex = 3
Original.Sheets(1).Rows(i).Font.ColorIndex = 2
l = 1 + l
Exit For
End If
Next k
Next i
Vielleicht hilft das schon. Trotzdem dürfte es nicht so lange dauern.
Holger
Bild

Betrifft: AW: Vergleich zweier Listen - zu langsam, bitte Hilfe!
von: Holger Levetzow
Geschrieben am: 14.03.2005 13:32:58
ich bin es nochmal. Lass Dir mal bis_i und bis_k ausgeben
z.B.
Debug.Print bis_i
es kann sein, dass diese Werte wesentlich größer als erwartet sind (Reste früherer Inhalte).
Holger
Bild

Betrifft: AW: Vergleich zweier Listen - zu langsam, bitte Hilfe!
von: Bernhard
Geschrieben am: 14.03.2005 16:22:18
Hallo Holger,
danke für den Tipp:
Damit habe ich das Ganze immerhin schon auf gut 5 Minuten reduziert. Immer noch recht langsam - aber schon machbar! Es sind übrigens in beiden Sheets 4045 Zeilen!
Bin für weitere Ideen sehr dankbar!
Grüße, Bernhard
4045
14.03.2005 16:12:08
1806 Übereinstimmungen
14.03.2005 16:17:39
Bild

Betrifft: AW: Vergleich zweier Listen - zu langsam, bitte Hilfe!
von: Holger Levetzow
Geschrieben am: 14.03.2005 13:23:08
Hallo Bernhard,
versuche mal, die Variablen nicht jedesmal zusammenzuführen/berechnen ..., z.B.
bis_i=Original.Sheets(1).UsedRange.Rows.Count
bis_k=CompareWith.Sheets(1).UsedRange.Rows.Count
For i = 1 To bis_i
wert=Original.Sheets(1).Cells(i, 11) & Original.Sheets(1).Cells(i, 12)
For k = 1 To bis_k
If wert = CompareWith.Sheets(1).Cells(k, 11) & CompareWith.Sheets(1).Cells(k, 12) Then
Original.Sheets(1).Rows(i).Interior.ColorIndex = 3
Original.Sheets(1).Rows(i).Font.ColorIndex = 2
l = 1 + l
Exit For
End If
Next k
Next i
Vielleicht hilft das schon. Trotzdem dürfte es nicht so lange dauern.
Holger
Bild

Betrifft: AW: Vergleich zweier Listen - zu langsam, bitte Hi
von: Kurt
Geschrieben am: 14.03.2005 16:54:39
hi,
probier mal:
Option Explicit

Sub CompareFiles()
Dim arr1
Dim arr2
Dim i As Long 'Schleifenzähler Zeilen
Dim k As Long
Dim zaehler As Double
Dim Original As Workbook
Dim CompareWith As Workbook
Const dir = "c:\Makro_Test"
Set Original = Workbooks(ActiveWorkbook.Name)
arr1 = Original.Sheets(1).Range(Cells(1, 11), Cells(Original.Sheets(1).UsedRange.Rows.Count, 12))
Application.Dialogs(xlDialogOpen).Show "c:\Makro_Test"
Set CompareWith = Workbooks(ActiveWorkbook.Name)
arr2 = CompareWith.Sheets(1).Range(Cells(1, 11), Cells(CompareWith.Sheets(1).UsedRange.Rows.Count, 12))
For i = LBound(arr1) To UBound(arr1)
For k = LBound(arr1) To UBound(arr2)
        If arr1(i, 1) & arr1(i, 2) = arr2(k, 1) & arr2(k, 2) Then
            Original.Sheets(1).Rows(i).Interior.ColorIndex = 3
            Original.Sheets(1).Rows(i).Font.ColorIndex = 2
            zaehler = zaehler + 1
        End If
Next k
Next i
    Debug.Print zaehler & " Übereinstimmungen"
    CompareWith.Close
End Sub

...und Tschüss Kurt
Bild

Betrifft: AW: Vergleich zweier Listen - zu langsam, bitte Hi
von: Bernhard
Geschrieben am: 14.03.2005 17:02:02
Hi Kurt,
vielen Dank - ich werde das durchprobieren!
Hast du vielleicht einen guten Link, wo mehr über Arrays erklärt wird (habe noch nicht gesucht) - ich habe bisher nicht damit gearbeitet.
Morgen werde ich es probieren!
Ciao
Bernhard
 Bild

Beiträge aus den Excel-Beispielen zum Thema "Vergleich zweier Listen - zu langsam, bitte Hilfe!"