Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Performance: Cells.Find vs For...Next

Betrifft: Performance: Cells.Find vs For...Next von: Robert
Geschrieben am: 05.08.2014 08:10:17

Hallo zusammen,

Ich frage mich grade, was denn schneller rechnet.
Zur Auswahl stehen:

Die Find Methode:

   Set Zelle = Columns(2).Find(what:="WERTXYZ")
        If Zelle <> Nothing Then
        Copy Paste
        End If

Und die For...Next Schleife:
For i = 2 To Lastrow
    If Cells(i, 2) = "WERTXYZ" Then
    Copy Paste
    End If
Next i
Viele Grüße
Robert

  

Betrifft: AW: Performance: Cells.Find vs For...Next von: fcs
Geschrieben am: 05.08.2014 08:19:18

Hallo Robert,

bei wenigen Zeilen wirst du kaum einen Unterschied merken.
Müssen mehrere 1000 Zeilen durchsucht werden, dann ist die Find-Methode schneller.

Wenn man sehr viele Daten durchsuchen/vergleichen muss, dann kann man beim Arbeiten mit For-Next die Daten vorher in ein Array überführen und dann mit dem Array arbeiten.

Gruß
Franz


  

Betrifft: AW: Performance: Cells.Find vs For...Next von: Robert
Geschrieben am: 05.08.2014 08:32:45

Danke für die schnelle Antwort.


  

Betrifft: AW: Performance: Cells.Find vs For...Next von: Daniel
Geschrieben am: 05.08.2014 08:33:56

Bei großen Datenmengen könnte es auch sinnvoll sein, die Daten zu sortieren und die Zeilennummer mit Worksheetfunction.Match zu ermitteln:

i = worksheetfunction.match("WERTXYZ", columns(2), 1)
If Cells(i, 2) = "WERTXYZ" then
    Copy Paste
End If
In sortierten Listen kann Worksheetfunction.Match eine schnellere Suchmethode verwenden und beim Sortieren ist Excel auch sehr schnell.

Gruß Daniel


  

Betrifft: Im Prinzip ist VBA schnell genug, wenn ... von: Luc:-?
Geschrieben am: 05.08.2014 11:05:38

…die richtige MethodenKombi verwendet wird, Robert,
also ein Datenfeld (Array) in Kombination mit einer For Each-Schleife.
Gruß, Luc :-?


  

Betrifft: Array Grundlagen von: Robert
Geschrieben am: 05.08.2014 11:15:08

Hallo Luc,

Klingt vielversprechend.
Ich muss ehrlich gestehen, das mit den Arrays hab ich noch nicht verstanden.
Es ist ja irgendwie eine Liste von Werten, die allerdings nicht dynamisch wächst, sondern deren Größe man vorgeben muss? Und dann gibts das Ganze noch in mehreren Dimensionen?

Ich hab mir zwar die bisher sehr hilfreichen TutorialSeiten zu diesem Theme durchgelesen, der tatsächliche Anwendungsfall bereitet mir aber Schwierigkeiten.

Wie gehe ich vor, wenn ich z.B. Folgendes Codebeispiel mit Arrays lösen will:

For i = Output.UsedRange.Rows.Count To 2 Step -1
        If WorksheetFunction.CountA(Output.UsedRange.Rows(i)) = 0 Then
           .UsedRange.Rows(i).EntireRow.Delete
        Else
        Set Match = Nothing
                Set Match = Mapping.Columns(20).Find(What:="WERTXYZ")
                If Not Match Is Nothing Then
                    If Me.ColumnBox1 <> "" Then 'Customer
                        Output.Cells(i, Me.ColumnBox1) = Mapping.Cells(Match.Row, 31)
                    End If
                    If Me.ColumnBox2 <> "" Then 'Material
                        Output.Cells(i, Me.ColumnBox2) = Mapping.Cells(Match.Row, 33)
                    End If
                    If Me.ColumnBox3 <> "" Then 'ProductFamily
                        Output.Cells(i, Me.ColumnBox3) = Mapping.Cells(Match.Row, 49)
                    End If
                End If
End If
Viele Grüße
Robert


  

Betrifft: Evtl später (heute Abend?)! owT von: Luc:-?
Geschrieben am: 05.08.2014 12:36:27

:-?


  

Betrifft: So, iss'n paar Abende später geworden, ... von: Luc:-?
Geschrieben am: 08.08.2014 16:29:05

…Robert,
weil ich unbedingt erst noch etwas Komplexeres fertigstellen wollte (s. OlXl-CP-Forum).
Wenn ich deinen PgmAuszug richtig interpretiere, trägst du die gefundenen Werte mehrfach ein. Davon geht jetzt auch mein Vorschlag aus, wobei auch die 1.Zeile des ZielBlattes kopiert wird und die ergänzten Angaben dann alle auf 1× in das wert-gelöschte ZielBlatt geschrieben wdn. Teste meinen Vorschlag also erst an einer Kopie. Mir war dein Vorhaben zu spezifisch, als dass ich das neue Pgm testen konnte und wollte. Es sollte ja wohl auch nur ein Bsp sein.
Was die Arrays betrifft, habe ich den 2.Typ gewählt → Variant mit einem Array, wobei ZBer dann konkret ein senkrechter, 1dimensionaler Vektor ist, dessen Elemente von horizontalen, 1dimensionalen Vektoren gebildet wdn. Dazu die folgd Anmerkung:
In Xl ist jeder Bereich stets 2dimensional, auch ein ZeilenVektor. In VBA sind nur ZeilenVektoren idR 1dimensional, SpaltenVektoren fast immer 2dimensional. Nur in der hier vorliegenden Form ist auch der SpaltenVektor 1dimensional. Wenn sowohl der HptVektor als auch die ElementeVektoren horizontal wären, könnte das Datenfeld nicht ohne Transformierung auf einen XlZellBereich abgebildet wdn.

    Const txSuBeg$ = "WERTXYZ"
    Dim ix As Long, iz As Long, zR As Long, CBox(1 To 3) As Long, _
        QBer, ZBer As Variant, urC1 As Range, xR As Range
    On Error GoTo fx
    ReDim ZBer(Output.UsedRange.Rows.Count - 1), QBer(3)
    With WorksheetFunction
        QBer(0) = .Transpose(Mapping.Columns(20))
        QBer(1) = .Transpose(Mapping.Columns(31)): CBox(1) = Me.ColumnBox1 'Customer
        QBer(2) = .Transpose(Mapping.Columns(33)): CBox(2) = Me.ColumnBox2 'Material
        QBer(3) = .Transpose(Mapping.Columns(49)): CBox(3) = Me.ColumnBox3 'ProductFamily
        For Each xR In Output.UsedRange.Rows
            If CBool(.CountA) Then ZBer(iz) = .Transpose(.Transpose(xR)): iz = iz + 1
        Next xR
        On Error Resume Next: zR = .Match(txSuBeg, QBer(0), 0): On Error GoTo fx
        If CBool(zR) Then
            For iz = 1 To UBound(ZBer)
                For ix = 1 To 3
                    If CBox(ix) <> "" Then ZBer(iz)(CBox(ix)) = QBer(ix)(zR)
                Next ix
            Next iz
        End If
    End With
    With Output.UsedRange
        Set urC1 = .Cells(1): .ClearContents
    End With
    urC1.Resize(UBound(ZBer) + 1, UBound(ZBer(0))) = ZBer
fx: If CBool(Err.Number) Then MsgBox Err.Description, vbCritical, _
        "Interner Fehler " & Err.Number: Set xR = Nothing
ex: Set urC1 = Nothing
Viel Spaß + schöWE, Luc :-?


  

Betrifft: Vielen Dank, Luc:-?! Verständnisfragen! von: Robert
Geschrieben am: 12.08.2014 12:37:50

Hallo Luc,

Der Code ist mir leider um einiges zu komplex, und beim 2ten absatz deiner Erklärung habe ich leider nur Bahnhof verstanden (und es hat ein paar traumatische (:P) Erinnerungen an MathematikVorlesungen in der Uni geweckt).
Ich versuche mal zu rekapitulieren:

ZBereich: Bereich mit einer Liste für jede Zeile im Output. Zbereich(12) gibt mir also eine Liste mit allen Werten der Reihe 12. Der ZBereich wird befüllt mit allen nicht leeren Zeilen von Output.

Frage: Wenn ich das so richtig verstanden habe, warum ist dann der Zbereich "zu Groß" definiert. Zählt das Programm nicht nur bei vollen Zeilen eins hoch, d.h. wenn 50% der Zeilen leer sind, ist das Array nur bis zur Hälfte beschrieben und die zweite Hälfte ist leer?
UND: muss, damit die Erste Zeile korrekt geschrieben wird, nicht zu Beginn iz auf 0 (oder 1?) gesetzt werden?

QBereich(3) = Array mit 4 Feldern für jede Zeile der Mappingliste, Felder werden gefüllt mit den entsprechenden Werten der Spalten 20, 31, 33 und 49 aus dem Mappingfile

Soviel zu Bereichen...

Match:
Jetzt sucht er die Konstante (die ich in meinem Programm irgendwie durch pro Zeile variablen Zellbezug in "Output" ersetzen muss) in der Liste QBer(0), also in Output, Spalte 20
Findet er was, geht er die drei Optionen durch und schreibt den Wert in die Entsprechende Liste an die Stelle, die der Spalte in der Combobox entspricht.

Frage: Um jetzt den Suchwert für jede Zeile in Output zu verändern, packe ich diesen Codeabschnitt

        On Error Resume Next: zR = .Match(txSuBeg, QBer(0), 0)
        On Error GoTo fx
        If CBool(zR) Then
            For iz = 1 To UBound(ZBer)
                For ix = 1 To 3
                    If CBox(ix) <> "" Then ZBer(iz)(CBox(ix)) = QBer(ix)(zR)
                Next ix
            Next iz
        End If

In eine Schleife mit x = 1 to UBound(ZBer)? Oder hab ich da nen knick in der Logik?

Weiter im Code:

Dann setzt er eine Range auf Zelle1 und löscht die Inhalte von Output.
Und bei der Letzten Zeile verließen sie ihn:
 urC1.Resize(UBound(ZBer) + 1, UBound(ZBer(0))) = ZBer
Du defnierst die Range neu, allerdings begreife ich Zber bzw Zber(0) nicht.
Hängt vermutlich mit einem Verständnisfehler weiter oben zusammen.

Bitte um weitere Hilfe! :)

viele Grüße und schonmal vielen vielen Dank
Robert


  

Betrifft: AW: So, iss'n paar Abende später geworden, ... von: Robert
Geschrieben am: 12.08.2014 12:49:56

Hallo Luc,

Der Code ist mir leider um einiges zu komplex, und beim 2ten absatz deiner Erklärung habe ich leider nur Bahnhof verstanden (und es hat ein paar traumatische (:P) Erinnerungen an MathematikVorlesungen in der Uni geweckt).
Ich versuche mal zu rekapitulieren:

ZBereich: Bereich mit einer Liste für jede Zeile im Output. Zbereich(12) gibt mir also eine Liste mit allen Werten der Reihe 12. Der ZBereich wird befüllt mit allen nicht leeren Zeilen von Output.

Frage: Wenn ich das so richtig verstanden habe, warum ist dann der Zbereich "zu Groß" definiert. Zählt das Programm nicht nur bei vollen Zeilen eins hoch, d.h. wenn 50% der Zeilen leer sind, ist das Array nur bis zur Hälfte beschrieben und die zweite Hälfte ist leer?
UND: muss, damit die Erste Zeile korrekt geschrieben wird, nicht zu Beginn iz auf 0 (oder 1?) gesetzt werden?

QBereich(3) = Array mit 4 Feldern für jede Zeile der Mappingliste, Felder werden gefüllt mit den entsprechenden Werten der Spalten 20, 31, 33 und 49 aus dem Mappingfile

Soviel zu Bereichen...

Match:
Jetzt sucht er die Konstante (die ich in meinem Programm irgendwie durch pro Zeile variablen Zellbezug in "Output" ersetzen muss) in der Liste QBer(0), also in Output, Spalte 20
Findet er was, geht er die drei Optionen durch und schreibt den Wert in die Entsprechende Liste an die Stelle, die der Spalte in der Combobox entspricht.

Frage: Um jetzt den Suchwert für jede Zeile in Output zu verändern, packe ich diesen Codeabschnitt
On Error Resume Next: zR = .Match(txSuBeg, QBer(0), 0)
On Error GoTo fx
If CBool(zR) Then
For iz = 1 To UBound(ZBer)
For ix = 1 To 3
If CBox(ix) <> "" Then ZBer(iz)(CBox(ix)) = QBer(ix)(zR)
Next ix
Next iz
End If

In eine Schleife mit x = 1 to UBound(ZBer)? Oder hab ich da nen knick in der Logik?

Weiter im Code:

Dann setzt er eine Range auf Zelle1 und löscht die Inhalte von Output.
Und bei der Letzten Zeile verließen sie ihn:
urC1.Resize(UBound(ZBer) + 1, UBound(ZBer(0))) = ZBer
Du defnierst die Range neu, allerdings begreife ich Zber bzw Zber(0) nicht.
Hängt vermutlich mit einem Verständnisfehler weiter oben zusammen.

Bitte um weitere Hilfe! :)

viele Grüße und schonmal vielen vielen Dank
Robert