Frage von Minenfeld4, 112

Spalten Vergleichen mit Excel VBA?

Ich habe in Tabelle "LogInv" in Spalte 2 ca. 1600 Werte. Nun soll überprüft werden ob in Tabelle "HSV" in Spalte 16 diese Werte vorhanden sind (ca. 3100 Werte). Wenn ja, wird die Zelle in "LogInv" grün markiert, wenn nein, wird nichts unternommen.

Ich habe bereits einen Code geschrieben, der auch funktioniert. Das Problem ist aber, dass er nur funktioniert wenn ich nur ein paar Zeilen überprüfen lasse. Wenn ich jedoch ALLE Zeilen vergleichen lasse stürzt jedes mal Excel ab.

Kann mir jemand einen Tipp geben wie ich dieses Problem Ressourcenschonender lösen könnte?

Hier der bereits bestehende Code:

For i = 2 To lastRowLogInv
        For j = 2 To lastRowHSV
            If Workbooks("FTC-Software_26.11.2015.xlsx").Worksheets("LogInv").Cells(i, 2) = Workbooks("FTC-Software_26.11.2015.xlsx").Worksheets("HSV").Cells(j, 16) Then
                Workbooks("FTC-Software_26.11.2015.xlsx").Worksheets("LogInv").Cells(i, 2).Interior.ColorIndex = 4
            End If
        Next j
        
        ProgressBar = i
        
        If i = lastRowLogInv Then
            MsgBox "Vorgang abgeschlosen!"
        End If
    Next i

Wie schon zu erkennen mache ich es mit zwei verschachtelten FOR-Schleifen. Geht das auch anders??

Danke im Vorraus

Expertenantwort
von Iamiam, Community-Experte für Excel, 81

Das geht m.E. auch viel einfacher:

Sind in Loginv!B:B keine Doppel, dann daneben in Zeile 1 (SpalteC muss leer sein, ansonten neue Spalte C einfügen- kann später verschoben werden) die Fo:

=Zählenwenn(HSV!P:P;B1)

nach Eingabe der Formel Doppelklick aufs Ausfüllkästchen: die Fo wird runterkopiert, solange SpalteB gefüllt ist.

Formatierst Du die Spalte dann noch mit dem Bendef Format

0;[rot]-0;[Farbe7]▬

dann siehst du sofort, welche Werte noch in P:P fehlen

Enthält Spalte B:B Doppel, dann die Formel etwas erweitern:

=Wenn(Zählenwenn(B$1:B1;B1)>1;Zählenwenn(B:B;B1)&"-faches Auftreten";Zählenwenn(HSV!P:P;B1))

Dabei treten allerdings die Texte auf, wo Doppel daneben stehen.(alternativ Lücken).

Ist das das, was Du erreichen wolltest? [Makros nur in zwingenden Fällen verwenden, Du brauchst Typ .xlsm und ein Benutzer erhält Warnungen und traut sich die Datei vllt nicht öffnen, ausserdem sind die integrierten Formeln viel schneller!

Oder wolltest Du Übungsmaterial für Makros? Dann schreib das!

Kommentar von Iamiam ,

Du kannst die Info bei Doppeln auch anders gestalten:

=Wenn(Zählenwenn(B$1:B1;B1)>1;Zählenwenn(B$1:B1;B1)&"-tes Auftreten";Zählenwenn(HSV!P:P;B1))

Kommentar von Iamiam ,

zum grün färben bedingte Formatierung, Formel verwenden. Für B1 die Formel:

=Zählenwenn(HSV!P:P;B1)>0

ergibt WAHR, wenn B1 in HSV!P:P auftaucht. Kein Absolutzeichen!!!

Format auf ganze Spalte anwenden vllt auch "nur" auf die 1600 Zellen.

Antwort
von Drakus86, 87

Könnte es an deiner Progressbar liegen?

Diese prüft ja nochmals, ob alle Werte passen und bei 3100 Werten könnte ich mir vorstellen, dass Excel dann streikt.

Hab es mal selber probiert mit 2000 Zeilen, Spalte A und B wurden beschriftet und Excel muss da schon bissl laden.

Kommentar von Minenfeld4 ,

Hab es jetzt anders gelöst. Ich lese jetzt zuerst die Spalte B in Tabelle LogInv in ein Array. Dann die Spalte P in HSV und mach den Vergleich dann innerhalb der Arrays. Geht um ein vielfaches schneller!

Das mit der Progressbar funktioniert aber trotzdem nicht richtig.

Danke für die Antwort

Expertenantwort
von Ninombre, Community-Experte für Excel, 63

Die könntest die Performance verbessern, wenn Du vor der For j= Schleife noch ein Worksheetsfunction.Countif einbaust. Wenn der Wert in der Zieltabelle nicht gefunden wird, dann muss die Schleife gar nicht durchlaufen werden.

Wenn ich nachher am Rechner bin, kann ich das konkreter beschreiben.

Kommentar von Minenfeld4 ,

Habs anders gelöst.

Trotzdem Danke :)

Antwort
von mrDoctor, 71

Hallo

Speichere deine Datei mal als Excel Binärdatenblatt ab. Vielleicht hilf das, dass das Programm nicht immer abstürzt.

Kommentar von Minenfeld4 ,

Leider nein, fast gleiches Problem.


Keine passende Antwort gefunden?

Fragen Sie die Community

Weitere Fragen mit Antworten