Ich habe mir für ein einzelnes Tabellenblatt in meiner Exceldatei folgenden 3-teiligen Code geschrieben.
Grob gesagt, geht es dabei um Neuberechnungen in drei Bereichen des Tabellenblattes in Abhängigkeit von (geänderten) Inhalten anderer Zellen des selben Tabellenblattes. Wobei die Neuberechnung im letzten Teil auf Änderung der im mittleren Teil berechneten Zellen basieren und durch Verwendung derselben Zellen aus dem mittleren Teil geschehen soll.
Mein Code funktioniert einwandfrei, wenn ich jeweils den 1. Teil entweder zusammen mit dem mittleren Teil oder zusammen mit dem letzten Teil laufen lasse.
Sobald ich aber alle 3 Teile zusammen laufen lasse, erhalte ich den Fehler Nr. 28 nicht genügend Stapelspeicher.
Anhand anderer Beiträge hier im Forum verstehe ich auch ungefähr, wo mein Problem zu liegen scheint. Offensichtlich ergibt sich aus dem Ablauf vom mittleren zusammen mit dem letzten Teil meines Codes eine Art Dauerschleife, die den Stapelspeicher volllaufen lässt (?).
Ich kriege aber, trotz Berücksichtigung der Erklärungen in anderen auffindbaren Forumsbeiträgen, die Lösung des Problems in meinem eigenen Code einfach nicht hin.
Kann mir da jemand von euch konkret weiterhelfen?
Mein Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
'-----TEIL 1 ------------------
Dim KeyCellsA As Range
Set KeyCellsA = Range("$D$22:$D$222")
If Not Application.Intersect(KeyCellsA, Range(Target.Address)) _
Is Nothing Then
Sheets("Umsatzplanung").Select
Application.Calculation = xlCalculationManual
Range("$F22:$J$222").ClearContents
Dim iRowA As Integer
iRowA = Cells(Rows.Count, 4).End(xlUp).Row
ActiveSheet.Range("$F22:F" & iRowA).FormulaLocal =_
"=WENN(Umsatzplanung!$D22="""";"""";SVERWEIS(Umsatzplanung!$D22;Datenüberprüfung!X:AC;4;0))"
ActiveSheet.Range("$H22:H" & iRowA).FormulaLocal =_
"=WENN(Umsatzplanung!$D22="""";"""";SVERWEIS(Umsatzplanung!$D22;Datenüberprüfung!X:AC;5;0))"
ActiveSheet.Range("$I22:I" & iRowA).FormulaLocal =_
"=WENN(Umsatzplanung!$D22="""";"""";SVERWEIS(Umsatzplanung!$D22;Datenüberprüfung!X:AC;6;0))"
Application.Calculation = xlAutomatic
Application.DisplayAlerts = False
ActiveSheet.Range("F22:I" & iRowA).Copy
ActiveSheet.Range("F22").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Application.DisplayAlerts = True
Range("D" & iRowA).Select
End If
'-----TEIL 2------------------
Dim KeyCellsB As Range
Set KeyCellsB = Range("$M$9:$Q$16", "$L$22:$L$222")
If Not Application.Intersect(KeyCellsB, Range(Target.Address)) _
Is Nothing Then
Sheets("Umsatzplanung").Select
Application.Calculation = xlCalculationManual
Range("$T22:$AC$222").ClearContents
Dim iRowB As Integer
iRowB = Cells(Rows.Count, 12).End(xlUp).Row
ActiveSheet.Range("$T22:T" & iRowB).FormulaLocal =_
"=WENN($A22=$S$11;SUMMEWENN($T$5:$AC$5;T$19;$T$11:$AC$11);WENN($A22=$S$12;_
SUMMEWENN($T$5:$AC$5;T$19;$T$12:$AC$12);WENN($A22=$S$13;_
SUMMEWENN($T$5:$AC$5;T$19;$T$13:$AC$13);WENN($A22=$S$14;_
SUMMEWENN($T$5:$AC$5;T$19;$T$14:$AC$14);WENN($A22=$S$15;_
SUMMEWENN($T$5:$AC$5;T$19;$T$15:$AC$15);WENN($A22=$S$16;_
SUMMEWENN($T$5:$AC$5;T$19;$T$16:$AC$16);""fehler""))))))"
ActiveSheet.Range("$U22:U" & iRowB).FormulaLocal =_
"=WENN($A22=$S$11;SUMMEWENN($T$5:$AC$5;U$19;$T$11:$AC$11);WENN($A22=$S$12;_
SUMMEWENN($T$5:$AC$5;U$19;$T$12:$AC$12);WENN($A22=$S$13;_
SUMMEWENN($T$5:$AC$5;U$19;$T$13:$AC$13);WENN($A22=$S$14;_
SUMMEWENN($T$5:$AC$5;U$19;$T$14:$AC$14);WENN($A22=$S$15;_
SUMMEWENN($T$5:$AC$5;U$19;$T$15:$AC$15);WENN($A22=$S$16;_
SUMMEWENN($T$5:$AC$5;U$19;$T$16:$AC$16);""fehler""))))))"
ActiveSheet.Range("$V22:V" & iRowB).FormulaLocal =_
"=WENN($A22=$S$11;SUMMEWENN($T$5:$AC$5;V$19;$T$11:$AC$11);WENN($A22=$S$12;_
SUMMEWENN($T$5:$AC$5;V$19;$T$12:$AC$12);WENN($A22=$S$13;_
SUMMEWENN($T$5:$AC$5;V$19;$T$13:$AC$13);WENN($A22=$S$14;_
SUMMEWENN($T$5:$AC$5;V$19;$T$14:$AC$14);WENN($A22=$S$15;_
SUMMEWENN($T$5:$AC$5;V$19;$T$15:$AC$15);WENN($A22=$S$16;_
SUMMEWENN($T$5:$AC$5;V$19;$T$16:$AC$16);""fehler""))))))"
ActiveSheet.Range("$W22:W" & iRowB).FormulaLocal =_
"=WENN($A22=$S$11;SUMMEWENN($T$5:$AC$5;W$19;$T$11:$AC$11);WENN($A22=$S$12;_
SUMMEWENN($T$5:$AC$5;W$19;$T$12:$AC$12);WENN($A22=$S$13;_
SUMMEWENN($T$5:$AC$5;W$19;$T$13:$AC$13);WENN($A22=$S$14;_
SUMMEWENN($T$5:$AC$5;W$19;$T$14:$AC$14);WENN($A22=$S$15;_
SUMMEWENN($T$5:$AC$5;W$19;$T$15:$AC$15);WENN($A22=$S$16;_
SUMMEWENN($T$5:$AC$5;W$19;$T$16:$AC$16);""fehler""))))))"
ActiveSheet.Range("$X22:X" & iRowB).FormulaLocal =_
"=WENN($A22=$S$11;SUMMEWENN($T$5:$AC$5;X$19;$T$11:$AC$11);WENN($A22=$S$12;_
SUMMEWENN($T$5:$AC$5;X$19;$T$12:$AC$12);WENN($A22=$S$13;_
SUMMEWENN($T$5:$AC$5;X$19;$T$13:$AC$13);WENN($A22=$S$14;_
SUMMEWENN($T$5:$AC$5;X$19;$T$14:$AC$14);WENN($A22=$S$15;_
SUMMEWENN($T$5:$AC$5;X$19;$T$15:$AC$15);WENN($A22=$S$16;_
SUMMEWENN($T$5:$AC$5;X$19;$T$16:$AC$16);""fehler""))))))"
ActiveSheet.Range("$Y22:Y" & iRowB).FormulaLocal =_
"=WENN($A22=$S$11;SUMMEWENN($T$5:$AC$5;Y$19;$T$11:$AC$11);WENN($A22=$S$12;_
SUMMEWENN($T$5:$AC$5;Y$19;$T$12:$AC$12);WENN($A22=$S$13;_
SUMMEWENN($T$5:$AC$5;Y$19;$T$13:$AC$13);WENN($A22=$S$14;_
SUMMEWENN($T$5:$AC$5;Y$19;$T$14:$AC$14);WENN($A22=$S$15;_
SUMMEWENN($T$5:$AC$5;Y$19;$T$15:$AC$15);WENN($A22=$S$16;_
SUMMEWENN($T$5:$AC$5;Y$19;$T$16:$AC$16);""fehler""))))))"
ActiveSheet.Range("$Z22:Z" & iRowB).FormulaLocal =_
"=WENN($A22=$S$11;SUMMEWENN($T$5:$AC$5;Z$19;$T$11:$AC$11);WENN($A22=$S$12;_
SUMMEWENN($T$5:$AC$5;Z$19;$T$12:$AC$12);WENN($A22=$S$13;_
SUMMEWENN($T$5:$AC$5;Z$19;$T$13:$AC$13);WENN($A22=$S$14;_
SUMMEWENN($T$5:$AC$5;Z$19;$T$14:$AC$14);WENN($A22=$S$15;_
SUMMEWENN($T$5:$AC$5;Z$19;$T$15:$AC$15);WENN($A22=$S$16;_
SUMMEWENN($T$5:$AC$5;Z$19;$T$16:$AC$16);""fehler""))))))"
ActiveSheet.Range("$AA22:AA" & iRowB).FormulaLocal =_
"=WENN($A22=$S$11;SUMMEWENN($T$5:$AC$5;AA$19;$T$11:$AC$11);WENN($A22=$S$12;_
SUMMEWENN($T$5:$AC$5;AA$19;$T$12:$AC$12);WENN($A22=$S$13;_
SUMMEWENN($T$5:$AC$5;AA$19;$T$13:$AC$13);WENN($A22=$S$14;_
SUMMEWENN($T$5:$AC$5;AA$19;$T$14:$AC$14);WENN($A22=$S$15;_
SUMMEWENN($T$5:$AC$5;AA$19;$T$15:$AC$15);WENN($A22=$S$16;_
SUMMEWENN($T$5:$AC$5;AA$19;$T$16:$AC$16);""fehler""))))))"
ActiveSheet.Range("$AB22:AB" & iRowB).FormulaLocal =_
"=WENN($A22=$S$11;SUMMEWENN($T$5:$AC$5;AB$19;$T$11:$AC$11);WENN($A22=$S$12;_
SUMMEWENN($T$5:$AC$5;AB$19;$T$12:$AC$12);WENN($A22=$S$13;_
SUMMEWENN($T$5:$AC$5;AB$19;$T$13:$AC$13);WENN($A22=$S$14;_
SUMMEWENN($T$5:$AC$5;AB$19;$T$14:$AC$14);WENN($A22=$S$15;_
SUMMEWENN($T$5:$AC$5;AB$19;$T$15:$AC$15);WENN($A22=$S$16;_
SUMMEWENN($T$5:$AC$5;AB$19;$T$16:$AC$16);""fehler""))))))"
ActiveSheet.Range("$AC22:AC" & iRowB).FormulaLocal =_
"=WENN($A22=$S$11;SUMMEWENN($T$5:$AC$5;AC$19;$T$11:$AC$11);WENN($A22=$S$12;_
SUMMEWENN($T$5:$AC$5;AC$19;$T$12:$AC$12);WENN($A22=$S$13;_
SUMMEWENN($T$5:$AC$5;AC$19;$T$13:$AC$13);WENN($A22=$S$14;_
SUMMEWENN($T$5:$AC$5;AC$19;$T$14:$AC$14);WENN($A22=$S$15;_
SUMMEWENN($T$5:$AC$5;AC$19;$T$15:$AC$15);WENN($A22=$S$16;_
SUMMEWENN($T$5:$AC$5;AC$19;$T$16:$AC$16);""fehler""))))))"
Application.Calculation = xlAutomatic
Application.DisplayAlerts = False
ActiveSheet.Range("T22:AC" & iRowB).Copy
ActiveSheet.Range("T22").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Application.DisplayAlerts = True
Range("M" & iRowB).Select
End If
'-----TEIL 3------------------
Dim KeyCellsC As Range
Set KeyCellsC = Range("$T$22:$AC$222")
' >>> das sind die berechneten Zellen aus Teil 2
If Not Application.Intersect(KeyCellsC, Range(Target.Address)) _
Is Nothing Then
Sheets("Umsatzplanung").Select
Application.Calculation = xlCalculationManual
Range("$M22:$M$222").ClearContents
Dim iRowC As Integer
iRowC = Cells(Rows.Count, 12).End(xlUp).Row
ActiveSheet.Range("$M22:M" & iRowC).FormulaLocal =_
"=WENN(L22="""";"""";(((((L22*(1-T22)-U22)*(1-V22)-W22)*(1-X22)-Y22)*(1-Z22)-AA22)*(1-AB22)-AC22))"
'>>> Berechnung mittels berechnete Zellen aus Teil 2
Application.Calculation = xlAutomatic
Application.DisplayAlerts = False
ActiveSheet.Range("M22:M" & iRowC).Copy
ActiveSheet.Range("M22").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Application.DisplayAlerts = True
Range("M" & iRowC).Select
End If
Application.ScreenUpdating = True
End Sub