Office: Dreifach "wenn" Abfrage mit VBA- gibt es eine bessere Lösung?

Hallo,

ich möchte aus einer Tabelle eine bestimmt Zelle finden, mit VBA.

Meine Vorgehensweise aktuell:

Code:

for Zeile= lng to 2 step -1
       If Cells(Zeile, 1)= "999" and Cells(Zeile, 2) = "666" and Cells(Zeile, 3) = "333" = True Then Test = Cells(Zeile, 4)
Next

Also wenn in Spalte 1 und Spalte2 und Spalte 3 ein Treffer gefunden wird, fülle die Variable "Test" mit dem Inhalt der Spalte 4.

Mit meiner If Anweisung arbeite ich jede Zeile und Spalte einzeln ab. Um eine bessere Performance zu erreichen, gibt es eine andere bzw bessere Lösung?
Vielleicht mit "find"?

Danke

 

Hi Marius,

verlasse die Schleife, wenn die Bedingung erfüllt ist

Code:

for Zeile= lng to 2 step -1
   If Cells(Zeile, 1)= "999" and Cells(Zeile, 2) = "666" and Cells(Zeile, 3) = "333" Then 
      Test = Cells(Zeile, 4)
      Exit For
   End If
Next

Bis später,
Karin

 

Um die Sache etwas performanter zu machen, könntest Du die einzelnen IF auftrennen. Ich bin mir nicht sicher, wie gut der Optimierer bei AND arbeitet, aber im schlimmsten Fall muß in jeder Zeile 3x ein String-Vergleich (langsam!) durchgeführt werden. Ein guter Optimierer würde bei AND nach dem ersten Vergleich abbrechen, wenn der "false" ergibt. Aber wie gesagt: Keine Ahnung, wie gut der VBA-Interpreter da optimiert.

Also sowas wie:

Code:

if (cells(zeile,1).value="999") then
    if (cells(zeile,2).value="666") then
        if (cells(zeile,3).value="333") then
            test = cells(zeile,4).value
        endif
    endif
endif

Das führt in jedem Fall dazu, wenn schon in y,1 nicht das "999" gefunden wird, daß die anderen Vergleiche gar nicht erst durchgeführt werden. Gerade bei String-Vergleichen (im Gegensatz zu numerischen Vergleichen) kann das einiges an Zeit sparen.

Mit etwas mehr Verwaltungs-Aufwand kannst Du natürlich auch Excel die Arbeit überlassen und einfach nach dem ersten Auftreten von "999" in Spalte 1 suchen lassen, also anstelle der Schleife in VBA, die das jetzt macht. Allerdings mußt Du dann trotzdem immer noch die Spalten 2 und 3 überprüfen. Das würde, je nachdem, wieviel Aufwand man in den Source stecken möchte, auch wieder mit der Excel-Suche gehen, wird dann aber langsam wirklich etwas unübersichtlich.

Ob es den Aufwand rechtfertigt, wage ich auch mal zu bezweifeln. Aber wie so oft ist die Antwort auf "Lohnt sich das?" auch hier wieder: "it depends..." Es hängt u.a primär davon ab, wie viele Treffer (prozentual) in der Liste sind.

Wenn Du die Möglichkeit hast, eine Hilfsspalte in das Blatt einzufügen, die ja auch ggf. später ausgeblendet werden kann, dann könntest Du das dort auch per Formel realisieren und in Deiner VBA-Schleife dann nur nach dieser Spalte gucken.

Also sowas wie:

Code:

E1: =wenn(und(a1="999";b1="666";c1="333");true;false)

(die Formel dann nach unten durchziehen)
Und VBA reduziert sich dann z.B. auf:

Code:

if (cells(zeile,5).value) then
    test = cells(zeile,4).value
endif

Und auch hier kann man dann natürlich auch Excel einfach nach dem ersten / nächsten Vorkommen von "true" suchen lassen.

Eine Lösung über den Autofilter wäre da sogar auch noch möglich, natürlich auch per VBA. Filter setzen, Kriterien setzen und dann per VBA nur noch die resultierenden Zeilen durchlaufen, am Ende den Autofilter wieder abschalten - et voila. (Das könnte letztendlich vielleicht sogar die schnellste Möglichkeit sein.)

Gruß, Michael

 

Hi Marius,

diese Variante ist bei sehr vielen Daten schneller als der And-Vergleich in einer Zeile, auf jeden Fall würde ich aber zusätzlich die Schleife verlassen wenn alle 3 Bedingungen erfüllt sind (siehe ergänzte rote Codezeile), damit nicht die restlichen Zeilen unnötig durchlaufen werden.

Du kannst auch mit Find arbeiten, wodurch nicht jede Zeile abgearbeitet werden muss - z.B so:

Code:

Dim rngZelle As Range
Dim strAdresse As String
Set rngZelle = Columns(1).Find("999", lookat:=xlWhole)
If Not rngZelle Is Nothing Then
   If Cells(rngZelle.Row, 2) = "666" And Cells(rngZelle.Row, 3) = "333" Then
      'Test = rngZelle.Offset(0, 3)
   Else
      strAdresse = rngZelle.Address
      Do
         If Cells(rngZelle.Row, 2) = "666" And Cells(rngZelle.Row, 3) = "333" Then
            Test = rngZelle.Offset(0, 3)
            Exit Do
         End If
         Set rngZelle = Columns(1).FindNext(rngZelle)
      Loop While Not rngZelle Is Nothing And strAdresse <> rngZelle.Address
   End If
End If
Set rngZelle = Nothing

Bis später,
Karin

 

Danke für die vielen Vorschläge!

Aber um ehrlich zu sein stehe ich noch am Anfang von VBA und um zu verstehen was ich dort mache, werde ich wohl bei meiner Methode bleiben!
Wenn ich etwas Fit bin in VBA werde ich mir eure Tipps aber nochmal vornehmen!

Vielen Dank!!!

 

Hi Marius,

ich habe meinen Code noch mal ein wenig überarbeitet und außerdem einige Kommentare eingefügt - vielleicht kannst du dann besser nachvollziehen, was jeweils geschieht

Code:

Dim rngZelle As Range      ' Variable für das Suchen der Zelle
Dim strAdresse As String   ' Variable für Adresse der 1. gefundenen Zelle
' finde in Spalte A "999" und vergleiche die genaue Übereinstimmung
Set rngZelle = Columns(1).Find("999", lookat:=xlWhole)
' der Begriff "999" wurde gefunden
If Not rngZelle Is Nothing Then
   ' Zelladresse der 1. gefundenen Zelle auf die Variable schreiben
   ' ist erforderlich um zu prüfen, wann der Suchprozess zu Ende und
   ' die gesamte Spalte durchsucht wurde
   strAdresse = rngZelle.Address
   ' Schleifenbeginn: führe aus
   Do
      ' in Spalte B der gefundenen Zeile steht "666" und in Spalte C steht "333"
      If Cells(rngZelle.Row, 2) = "666" And Cells(rngZelle.Row, 3) = "333" Then
         ' Wert aus der gefundenen Zelle jedoch in Spalte D auf die Variable schreiben
         Test = rngZelle.Offset(0, 4)
         ' Schleife verlassen
         Exit Do
      End If
      ' suche die nächste Zelle mit dem Inhalt "999" in Spalte A
      Set rngZelle = Columns(1).FindNext(rngZelle)
   ' wiederhole den Vorgang immer wieder, so lange die gefundene Zelladresse nicht der Startadresse
   ' des 1. Auftretens von "999" entspricht
   ' = Schleifen-Ende
   Loop While Not rngZelle Is Nothing And strAdresse <> rngZelle.Address
End If
' Variable leeren
Set rngZelle = Nothing

Bis später,
Karin

 

Hallo!

Danke! Jetzt verstehe ich auch was da los ist!!!

Vielen vielen DANK!!!!