Summewenn VBA zu langsam

Bild

Betrifft: Summewenn VBA zu langsam
von: Bonduca
Geschrieben am: 20.09.2015 20:24:39

Hallo,
ich habe einen Code der leider für ca 20000 Zeilen und 500 Spalten viel zu lange dauert.
Ich möchte eine Summewenn Funktion in Excel VBA einbauen.
In Tabelle 1 stehen meine Ausgangsdaten.
Wenn in Spalte C der Tabelle 2 die gleichen Werte stehen wie in Spalte C der Tabelle 1, dann sollen die einzelnen Spalten der Tabelle 1 zusammengezählt werden.


Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim varResult As Variant
Dim arrResult(20000) As Variant
   
Set ws1 = Worksheets("Tabelle1")
Set ws2 = Worksheets("Tabelle2")
ws2.Activate
'SummeWenn wird durchgeführt
 For i = 17 To 20000
For j = 4 to 500
 
     varResult = Application.WorksheetFunction.SumIfs( _
                    Arg1:=ws1.Range(ws1.Cells(17, 3), ws1.Cells(20000, 3)), _
                    Arg2:=ws2.Cells(i, 3), _
                    Arg4:=ws1.Range(ws1.Cells(17, j), ws1.Cells(20000, j)), _
'Ergebnis wird in einen Speicher geschrieben
Ergebnis (i,j) = varResult
Next j
 Next i
'Das Ergebnis wird in die einzelnen Zeilen geschrieben
For a = 17 To 20000
For b = 4 to 500
Cells(a, b) = Ergebnis (i,j)
Next b
Next a
End Sub

Der Code funktioniert, nur werde ich alt und grau bis er fertig berechnet hat.
Das muss doch irgendwie schneller gehen, oder?

Bild

Betrifft: Code ist nicht lauffähig!
von: Sepp
Geschrieben am: 20.09.2015 20:36:09
Hallo Bonduca,
glaube nicht, dass der Code so überhaupt läuft!

Gruß Sepp


Bild

Betrifft: AW: Summewenn VBA zu langsam
von: Bonduca
Geschrieben am: 21.09.2015 10:29:27
Ich hab jetzt nur zusammenkopiert, weil ich die genaue Bezeichnung hier nicht zeigen darf.
Ja, ich hab nicht alle Array deklariert. Aber ich denke man bekommt einen Anhaltspunkt was ich machen wollte.
Hat jemand eine Idee wie es schneller geht?

Bild

Betrifft: AW: Summewenn VBA zu langsam
von: Rudi Maintaire
Geschrieben am: 21.09.2015 10:59:55
Hallo,
zumindest das Ergebnis kannst du ohne Schleife schreiben.
Cells(17,4).resize(19984,497)=Ergebnis
Ansonsten dauert es halt, fast 10Mio Summewenn() zu berechnen.
Gruß
Rudi

Bild

Betrifft: AW: Summewenn VBA zu langsam
von: Bonduca
Geschrieben am: 24.09.2015 10:40:25
Vielen Dank für die Hinweise. Es hat leider etwas gedauert bis ich antworten konnte, weil mein PC gestreikt hat.
Leider hat das mit dem Cells(17,4).resize(19984,497)=Ergebnis nicht geklappt.
Mein Array füllt sich ja mit einer Schleife ( Ergebnis (i,j) ). Kann es sein, dass man es deshalb nicht auf einmal zurückgeben kann?
@Daniel: Danke. Die Formel hat funktioniert.

Bild

Betrifft: noch ne Frage
von: Rudi Maintaire
Geschrieben am: 21.09.2015 11:03:01
Hallo,
sind denn in ws2!C17:C20000 alle Werte unterschiedlich? Wenn nicht, hast du einen Haufen überflüssige Berechnungen.
Gruß
Rudi

Bild

Betrifft: AW: Summewenn VBA zu langsam
von: Sepp
Geschrieben am: 21.09.2015 19:06:06
Hallo Bonduca,
wieso kannst du den Code nicht zeigen? Gestohlen?

Gruß Sepp


Bild

Betrifft: AW: Summewenn VBA zu langsam
von: Daniel
Geschrieben am: 21.09.2015 19:32:58
Hi
bei der Datenmenge solltest du die Daten etwas aufbereiten, damit du schneller rechnen kannst.
1. Sortiere die Tabelle1 nach Spalte C
2. füge in der Tabelle2 zwei Hilfsspalten ein (D und E) mit folgenden Formeln (für zeile2, da Zeile1 Überschrift):
D17: =Vergleich(C17;Tabelle1!$C$17:$C$20000;0)
E17: =Vergleich(C17;Tabelle1!$C$17:$C$20000;1)
die Formeln geben dir die erste und letzte Zeilennummer des Wertes in der Tabelle1
3. ab Spalte F kannst du dann die Formel mit folgender Formel bilden:
F17: =Wenn(IstFehler($D17);"";Summe(Index(Tabelle1!D$17:D$20000;$D17):Index(Tabelle1!D$17:D$20000;$E17)))
Gruß Daniel

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Summewenn VBA zu langsam"