Live-Forum - Die aktuellen Beiträge
Datum
Titel
29.03.2024 13:14:12
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1860to1864
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Zellberechnung stoppen wenn Bedingung ..

Zellberechnung stoppen wenn Bedingung ..
08.12.2021 14:21:30
HP
Hallo alle zusammen,
ich bin neu hier und absoluter VBA-Anfänger. Mein Problem lässt sich aber wohl nur auf diesem Wege lösen.
Ich habe eine Bearbeitungsmatrix welche von mehreren Bearbeitern als "Rohdatei" kopiert und dann ausgefüllt wird. Für diese Datei suche ich eine Lösung die etwa wie folgt funktioniert:
Wenn in Zelle E3 der "*Beispieltext*" steht dann soll die Neuberechnung im Zellbereich J444:N444 gestoppt werden. Danach soll aber normal weitergerechnet werden.
Hierzu ist zu erwähnen, dass die Matrix sehr viele Wenn-Dann-Formeln enthält. Der Text in Zelle E3 wird aufgrund einer solchen Formel erzeugt, enthält aber unter bestimmten Bedingungen immer den Textteil "Beispieltext". Die Zellen J444 bis N444 werden ebenfalls mittels Formeln gefüllt. Das ganze soll nur auf einem bestimmten Arbeitsblatt der Matrix funktionieren. Die Bearbeiter sollen von dem automatischen Ablauf des Codes nichts mitbekommen.
Ich habe eine ähnliche VBA-Lösung gefunden und versucht sie wie folgt zu modifizieren:

Private Sub Worksheet_SelectChange(ByVal Target As Range)
Sub Festwerte()
Application.Calculation=xlCalculationManual
Dim Zelle as Range
For Each Zelle in Range("J444:N444")
If Range ("E3") = "*Beispieltext*" Then
Zelle = Zelle.Value
End If
Next Zelle
Application.Calculation = xlCalculationAutomatic
End Sub
End Sub Das hat nicht funktioniert. Hierbei kommt eine Fehlermeldung, wonach ein "End Sub " gefordert wird. Ich habe es auch schon mit mehreren unterschiedlichen Prozeduren aus dem Bereich Worksheet (z.B. Calculate) versucht. Es kamen jedoch immer Fehlermeldungen (z.B. Listenfehler), welche ich mit meinem beschränkten VBA-Wissen versucht habe zu korrigieren, was jedesmal fehlschlug.
Ich hoffe ich habe eine einigemaßen nachvollziehbare Beschreibung meines Problems gegeben. Vielleicht kann mir jemand bei der Lösung helfen.
Hierfür schon einmal vielen Dank
Viele Grüße HP

13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zellberechnung stoppen wenn Bedingung ..
08.12.2021 14:30:31
Pierre
Hallo,
also das "End Sub wird gefordert" liegt wahrscheinlich daran, dass du

Private Sub Worksheet_SelectChange(ByVal Target As Range)
und direkt darunter wieder ein Sub öffnest:

Sub Festwerte()
Entscheide dich für eins von beiden.
Gruß Pierre
noch was:
08.12.2021 14:31:31
Pierre
"SelectChange" gibt es soweit ich weiß nicht
Es heißt richtig "SelectionChange"
AW: noch was:
08.12.2021 14:40:43
HP
Sorry Pierre,
da habe ich wohl zu schnell geschrieben. Im Original steht in meinem Code natürlich SelectionChange.
Viele Grüße
HP
AW: noch was:
08.12.2021 14:45:50
Pierre
Hallo HP,
dann ist ja gut :)
Das vorherige hast du auch beachtet bzw. geändert?
Läuft dann das Makro?
Gruß Pierre
Anzeige
AW: noch was:
08.12.2021 14:50:00
HP
Hallo Pierre,
ich habe sowohl ein End Sub als auch das Sub Festwert rausgenommen. Leider funktioniert das ganze immer noch nicht. Ein Fehlerwert wird aber auch nicht ausgegeben.
Viele Grüße
HP
AW: noch was:
08.12.2021 15:38:37
Pierre
Dann wirds jetzt Zeit, deine Datei zu zeigen (anonymisiert natürlich) MIT Makro.
Ich bin mir allerdings nicht sicher, ob ich helfen kann.
Aber wenn du die Datei hier zur Verfügung stellst, Frage als Offen kennzeichnen, dann kümmert sich hoffentlich jemand darum.
Gruß Pierre
Jetzt mit Dateianhang
09.12.2021 09:05:04
HP
Vielen Dank zunächst mal an Pierre und Yal. Das mit dem End Sub habe ich jetzt verstanden.
Ich füge jetzt mal die Datei nebst Makro bei. Hoffentlich kann mir jemand bei meinem Problem helfen.
https://www.herber.de/bbs/user/149699.xlsm
Viele Grüße
HP
Anzeige
AW: Jetzt mit Dateianhang
09.12.2021 21:36:43
Yal
Hallo HP,
bevor Du zu lange auf einer Antwort wartest: deine Datei ist nicht genug sprechend, um zu nachvollziehen, was passieren soll.
Kritische Daten zu löschen ist gut, aber es müsste irgenwelche Daten vorhanden sein: verfälschte, Dummy, was auch immer.
Und deine Beschreibung ist nicht ausführlich und eindeutig genug:
was bedeutet Stoppen? soll die Makro nicht weitermachen?
Was passiert davor, Schritt für Schritt, was passiert danach, usw.
Wenn Du schon eine genau Beschreibung für Dich selbst hast, dann kommst Du vielleicht auf die Lösung. Wenn nicht, hilft es dem Helfer, Dir zu helfen.
Eine Wenn-Formel im Bereich "J444:N444" ist wahrscheinlich nicht aureichend, weil es zwar rechnen kann, wenn es ihr erlaubt ist, aber nicht den vorigen berechnete Wert zeigen kann, wenn es ihr nicht erlaubt ist, die Daten neuzurechen. Sehe ich das richig?
Eigentlich brauchst Du in "J444:N444" einen Wenn
= Wenn (IstFehler(Finden("Beispiel"; E3)); **Rechnungsformel**; J445)
und das Ereignis Worksheet_Calculate abzufangen, das das aktuelle Ergebnis in J444:N444 in J445:N445 vor jeder Rechnung als Wert kopiert.
VG
Yal
Anzeige
AW: Jetzt mit Dateianhang
09.12.2021 23:48:27
HP
Hallo Yal,
danke für Deine ausführliche Info. Ich will mal versuchen mein Problem genauer zu beschreiben.
In der Regel sind die in der Datei gruppierten Spalten I, K und N ausgeblendet, da sie für den Normalfall nicht benötigt werden. Dann steht in der Zelle E3 das Wort Test. Es gibt allerdings einige Fälle, in denen die Spalten I oder K oder N eine ganze Zeit nach der ursprünglichen Eingabe eingeblendet und, insbesondere die Zeilen auf Seite 2, gefüllt werden. Wenn dies geschieht, werden sehr häufig auch die Werte in den Zellbereichen J, L, M 50 bis 77 geändert und auch in Zelle E3 jeweils andere Texte eingespielt. Diese Änderungen wirken sich natürlich, wegen der diversen Wenn-Dann-Formeln, auf viele Bereiche der Datei aus. in der Regel ist das auch so gewollt. Nur in den Zellen J355 bis N355 ist das nicht gewollt. Dort sollen die Werte der ursprünglichen Berechnung erhalten bleiben und nicht neu berechnet werden. Eine entsprechende Neuberechnung erfolgt nämlich in dem Bereich J365 bis N365. Damit soll eine Vergleichbarkeit der alten mit den neuen Daten in diesem Bereich erreicht werden.
Für ein ganzes Arbeitsblatt kann ich die automatische Berechnung ja über Formeln, Berechnungsoptionen ausschalten. Ich möchte jedoch nur den kleinen Zellbereich J355 bis N355 von der Berechnung ausnehmen.
Um zu testen ob mein selbstgebasteltes Makro funktioniert habe ich immer ein paar Zahlen in die Zelle I38 geschrieben (dann ändert sich der Text in Zelle E3) und in Zelle J 75 eine weitere Zahl angefügt und dann geschaut, ob sich der Wert in Zelle J355 geändert hat oder nicht.. Dabei habe ich dann feststellen müssen, das mein Makro bisher nicht funktioniert.
Ich lade die Datei noch einmal mit ein paar Spieldaten hoch. Da kann man dann vielleicht die genaueren Bezüge erkennen.
https://www.herber.de/bbs/user/149727.xlsm
Hier noch einmal das Makro, welches ich mir als Anfänger dazu zusammengebaut habe, was aber leider nicht funktioniert.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculation = xlCalculationManual
Dim Zelle As Range
For Each Zelle In Range("J355:N355")
If Range("E3") = "*Beispieltext*" Then
Zelle = Zelle.Value
End If
Next Zelle
Application.Calculation = xlCalculationAutomatic
End Sub
Es wäre super, wenn mir jemand bei meinem Problem helfen könnte.
Viele Grüße
HP
Anzeige
AW: Jetzt mit Dateianhang
10.12.2021 07:11:55
ralf_b
Moin,
zunächst habe auch ich Fragen.
Ist es gewollt das im Bereich "J355:N355" die Formeln dauerhaft verschwinden und nur die Werte stehen bleiben?
Wenn ja, ab wann genau soll das passieren? Wenn sich E3 ändert ist es schon zu spät. Vba kann viel mehr.
Das SelectionChange Ereignis ist aus meiner Sicht nicht geeignet. Da es bei jedem Klick (nicht Änderung) auf irgend eine Zelle ausgelöst wird.
Evtl. das Change(bei Zelledit) . Hier wird auch das Target mitgeliefert und kann auf die ursächlich geänderte Zelle geprüft werden.
Beide Events Calculate und Change, werden aber erst ausgelöst wenn die Berechnung des Blattes abgeschlossen ist. So gesehen ist dort die Prüfung auf den Beispieltext zu spät. Da die Textänderung in E3 durch eine Berechnung erfolgt, reagiert das Change und SelectionChange Ereignis hier nicht.
Ich denke das man die Prüfung anders angehen muß. Aber dazu ist mir das ganze Konstrukt noch zu komplex.
Da du in der Datei eine Userform hast, überlege ich ob du die Eingaben evtl. später per Userform machen lassen willst. Dann würde die ganze Geschichte hier überflüßig werden, da man bei Eingabe in der Userform schon Werte checken kann und sich das Theater auf der Tabelle erübrigt.
gruß
rb
Anzeige
AW: Jetzt mit Dateianhang
10.12.2021 10:00:37
HP
Hallo rb,
Danke für Deine Info. Bezüglich des Ereignisses SelectionChange habe ich im Endeffekt das Ereignis genommen, welches mir Worksheet zuerst angeboten hat. Ich habe allerdings, ohne zu wissen was sie genau auslösen, auch andere Ereignisse z.B. Calculate ausprobiert, was aber auch nicht geklappt hat.
Wenn es möglich ist, die Berechnung der Zellen J355 bis N355 zu stoppen und die Formeln dabei zu erhalten, wäre das großartig. Ich wusste nicht, dass das geht. Bin halt ein Anfänger in VBA.
Die Datei wird vom Anwender von oben nach unten bearbeitet. Daher war meine Überlegung, wenn sich der Text in Zelle E3 ändert, was vor einer Änderung z.B. der Zelle J76 erfolgt, bleibt der alte Wert in den Zellen J355 bis N355 erhalten, wenn denn der Code funktioniert. Wenn ich Dich richtig verstanden habe, trifft das nicht zu, weil Excel entweder schon eine Berechnungsentscheidung getroffen hat ("Wenn sich E3 ändert ist es zu spät") oder eben erst nach Abschluss der Eingabe eine Neuberechnung durchführt.
UserForms habe ich so verstanden, dass es sich um Checkboxen oder ggf. auch Listenfelder handelt, richtig? Checkboxen möchte ich nicht einfügen, die Anwender arbeiten seit mehreren Jahren mit dieser Datei und sind an die Eingaben und Darstellungen gewöhnt. Daher dachte ich daran, dass das von mir gewünschte Ereignis im Hintergrund, ohne Beteiligung der Anwender, ablaufen kann. Über ein Listenfeld könnte man nachdenken, wenn die richtige Auswahlmöglichkeit besteht. Das muss ich mir aber erst noch mal durch den Kopf gehen lassen.
Sollte es tatsächlich nicht möglich sein, mein Problem "im Hintergrund" zu lösen, müsste ich halt damit leben, dass ich und die anderen Anwender die Zellen J355 bis N355 weiterhin händisch korrigieren.
Aber vielleicht fällt ja noch jemanden eine Lösung ein.
Danke schon mal für Euer Engagement
HP
Anzeige
AW: Jetzt mit Dateianhang
11.12.2021 01:11:58
ralf_b
Hallo HP,
der Beispieltext wird indirekt durch die Zellen I38, K38, N38 verändert. Also sollte man dort auch ansetzen.
So wie ich mittlerweile deine Datei verstanden habe, sind die grünen Bereiche Eingabebereiche.
Du willst das woanders Formeln nicht berechnet werden, wenn Werte dort eingegeben werden. Somit würde es ausreichen die ersten Zellen der Eingabebereiche zu überwachen. Dies wäre schon bei der Selektion möglich mit Selectionchange. In dem Moment könnte man die Formeln irgendwo auf dem Blatt sichern und an deren Stelle Werte setzen. Da ich aber nicht genau weis wofür die Formeln später wieder benötigt werden, mache ich mir darüber erstmal keine Gedanken.
Aber zu deinen Formeln habe ich Anmerkungen.
Bei nachfolgender Formel wird mit ODER geprüft. Gehört hier nicht ein UND hin?

=WENN(ODER(ISTZAHL(F95);ISTZAHL(F96));F95-F96;"")
diese Formel

=WENN(ISTZAHL(I97);WENN(UND(F1010;(-F101)>=I97);F101+I97;"0")+WENN(UND(F101
'könnte verkürzt werden. Da in jedem Term F101&lt0 steht, kann das auch vor alle gesetzt werden.

=WENN(ISTZAHL(I97);WENN(F101=I97;F101+I97;0));0);"") 

Anzeige
Verschachtelung Sub in Sub geht nicht
08.12.2021 17:24:50
Yal
Hallo HP,
Ich gehe davon aus, dass das "End Sub", das in deinem Beitrag in dem grünen Text nach dem Code-Bereich auftaucht, zu dem Code gehört.
Damit hättest Du eine Verschachtelung von Prozeduren ("Sub" und "Function" sind Prozeduren), was in VBA nicht geht (*).
Mache aus

Sub Worksheet_SelectionChange()
Sub FestWerte()
'... Festwert-Anweisungen
End Sub
End Sub
etwas wie

Sub Worksheet_SelectChange()
Call FestWerte
End Sub
Sub FestWerte()
'... Festwert-Anweisungen
End Sub
(der "Call" ist nicht notwendig. Aber es verdeutlich die Funktionsweise).
Oder gar direkt

Sub Worksheet_SelectChange()
'... Festwert-Anweisungen
End Sub
Eine abgelegte Prozedure ist nur ratsam, wenn
_ der Aufruf an verschiedenen Stellen sich wiederholt,
_ die Aufgabe so ausführlich (viele Zeilen), dass der Leseflow im Hauptprozedure davon gestört wird,
_ eine Fehler-Behandlung für die Teil-Aufgabe muss erfolgen (Fehler-Behandlung gelten immer für eine Prodzedure).
(ich hoffe, ich verwirre dich damit nicht zusehr)
(*): das merkst Du, wenn Du das erste Beispiel im VBA-Editor kopierst und am Ende der Zeile "Sub Text()" Enter drückst:
VBA fügt sofort drunter einen "End Sub", weil es vor dem nächsten Prozedure ("Sub xxx") immer zuerst einen "End Sub" geben muss.
VG
Yal
Anzeige

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige