VBA, Neu berechnen bis ein Bezugswert erreicht oder überschritten wird?
Hallo VBA-Profis,
ich hätte gerne ein VBA, dass eine Neuberechnung solange automatisch (durch Tasten-Kombination) von einer Bezugszelle durchführt bis ein bestimmter Wert erreicht, oder überschritten wird (angenommen Wert 100 oder größer). Durch F9 manuell zu aufwendig und langwierig.
Sollte die Bezugszelle den Wert erreichen oder überschreiten, dann sollen Daten von bestimmten Zellen auf andere bestimmte Zellen übertragen und abgespeichert werden, damit die Werte erhalten bleiben.
Als Beispiel siehe detaillierte Abbildung für die Bezüge.
Ich freue mich, wenn es hierfür eine Lösung gäbe und bedanke mich im Voraus für Eure Bemühungen.
Gruß Tron
Hallo Zusammen,
noch eine Zusatzinformation.
Wenn der Wert 100 erreicht oder überschritten wird.
Gruß Tron
Hallo Zusammen,
besser erklärt (26.01.2025).
Ich brauche lediglich ein VBA-Syntax, dass solange automatisch neu berechnet wird bis der Wert in Zelle CC27 minimal 100 oder größer ist.
Den Rest kriege ich irgendwie hin, was den Übertrag von den Zellen BZ33 bis CC33 in andere Zellen gewollt ist.
Makro werde ich über eine Schaltfläche ausführen.
Gruß Tron
Hallo Zusammen,
anbei die Abbildung des VBA getestet der bei mir leider nicht funktioniert.
Gruß Tron
Was hat es mit den "x" in der Bezugszelle auf sich. Dies verhindert eine "einfache" numerische Prüfung.
Hallo,
das "x" bedeutet nur die Auswahl der Daten.
Also selektierbar von 1 bis 20 Daten.
Gruß Tron
1 Antwort
Also es gibt irgendeinen Zirkelbezug und du hast bei der Arbeitsmappe die Funktion "Iterative Berechnung" aktiviert, damit bei jeder Berechnung irgendein Wert basierend auf dem vorherigen Wert berechnet wird?
Ein VBA-Code wäre ziemlich einfach:
Public Sub autoRefresh()
While Selection.Value < 100
ActiveSheet.Calculate
Wend
End Sub
Eine Tastenkombination kannst du zuweisen, indem du auf der Registerkarte "Entwicklertools" auf "Makros" klickst, dann die Prozedur auswählst (also in diesem Fall "Tabelle1.autoRefresh") und dann unter "Optionen..." eine Tastenkombination festlegst.
Ich hab keine Ahnung, was du da berechnen willst, aber ich bin mir ziemlich sicher, dass sich das auch über eine Formel lösen lassen würde.
Hi Tron,
soll immer der Wert aus CC27 beobachtet werden? Der Code, den ich oben geschrieben hab, bezieht sich immer auf die aktuell ausgewählte Zelle. Also wenn du CC27 auswählst und dann das Makro ausführst, wird die Tabelle so oft neu berechnet, bis CC27 mindestens 100 ist. Wenn du stattdessen z.B. CA27 auswählst und das Makros ausführst, wird stattdessen CA27 beobachtet.
Du kannst das Makro aber natürlich auch auf CC27 festlegen:
Public Sub autoRefresh()
While [CC27].Value < 100
ActiveSheet.Calculate
Wend
End Sub
Das mit dem "<" ist so richtig. So lange der Wert unter 100 ist, wird das Blatt immer wieder neu berechnet. Heißt im Umkehrschluss, dass die Berechnung beendet wird, wenn der Wert >= 100 ist.
Den Code müsstest du in das Modul für das jeweilige Tabellenblatt reinmachen. Also kein neues Modul, sondern das, was schon da ist. In meinem Test heißt das einfach "Tabelle 1".
Beim Kopieren der Daten kommt es auch drauf an: Werden immer genau die vier Zellen BZ33 bis CC33 gefüllt oder können auch mal mehr oder weniger Zellen befüllt werden?
Wenn es immer die vier sind, geht dieser Code:
Public Sub autoRefresh()
' Tabelle neu berechnen, bis CC27 >= 100 ist
While [CC27].Value < 100
ActiveSheet.Calculate
Wend
' erste leere Zeile finden
Dim destRow As Integer ' Zeile, in die das Ergebnis kopiert werden soll
Dim searchRange As Range, searchCell As Variant
Set searchRange = Range("BZ53:BZ1000")
For Each searchCell In searchRange
If searchCell.Value = "" Then
destRow = searchCell.Row
Exit For
End If
Next
' Werte kopieren
Range("BZ" & destRow & ":CC" & destRow).Value = Range("BZ33:CC33").Value
End Sub
Ich hab bei meinem Test nur festgestellt, dass Excel auch nochmal alles neu berechnet, wenn die Werte aus Zeile 33 in den Ergebnisbereich kopiert werden. Das heißt, die Werte, die unten reinkopiert werden, wurden gesammelt als CC27 >= 100 war, aber nachdem die Werte reinkopiert werden, steht in CC27 wieder was anderes drin.
Um das Problem zu beheben, könntest du bei den Berechnungsoptionen "manuell" einstellen, damit das Arbeitsblatt nicht bei jeder Änderung neu berechnet wird. Das hätte mit meinem Code nur das Problem, dass er nichts neu berechnet, wenn noch die letzte Zahl in CC27 steht, die ja schon größer oder gleich 100 sein muss, damit die Prozedur durchläuft. Dadurch würde sie immer wieder die letzten Werte kopieren, wenn du sie neu ausführst. Oder du drückst vorher F9, damit das Blatt einmal neu berechnet wirst oder machst in die Prozedur am Anfang ein ActiveSheet.Calculate rein, damit die Prozedur automatisch mindestens einmal das Blatt neu berechnet.
Hi,
1000 Dank !
Meine Berechnungsoptionen stehen auf "Manuell", die ich so festgelegt habe damit die Daten kopiert werden können ohne das eine ständige Berechnung erfolgt.
Ich werde es Testen sobald mein gesundheitlicher Zustand es zulässt (Massiv angeschlagen).
Ich melde dann wieder.
Hi,
ich habe es jetzt getestet.
VBA Variante 1, bringt mir "Fehler beim Kompilieren: Syntaxfehler", While [CC27].Value < 100 wird blau markiert.
VBA Variante 2, gab mir zuerst den Fehler "Fehler beim Kompilieren: Sub, Function oder Property erwartet, blaue Markierung Anfang in der 2 Zeile vor Info-Text.
Nach entfernen des Info-Textes, dann auch "Syntaxfehler" wie bei Variante.
Ich habe Office 365.
Gruß Tron
Hi Tron, der Zellbezug [CC27] funktioniert nur, wenn du den Code in das Modul für die jeweilige Tabelle schreibst. Du hast den Code wahrscheinlich in das Modul für die ganze Arbeitsmappe oder in ein neues Modul geschrieben.
Wenn du den Code in einem anderen Modul haben willst, musst du zusätzlich angeben, in welcher Tabelle die Zelle ist. Also z.B. so:
Sub autoRefresh()
Dim blatt As Worksheet
Set blatt = Sheets("Tabelle1")
' Statt Tabelle1 musst du halt den Namen reinschreiben, den dein Tabellenblatt hat
While blatt.Range("CC27").Value < 100
blatt.Calculate
Wend
End Sub
Hi daCypher,
1000 Dank, auch für die vorangegangenen präzise Erläuterungen.
Soweit bin ich gesundheitlich wieder OK. Check es bei nächsten Gelegenheit, drängelt nicht.
Zwischenzeitlich habe ich mir was selber über "Makro aufzeichnen" etwas kreiert, war zwar nicht das was ich angefragt habe, war aber temporär für meine Zwecke OK.
Nebenbei bemerkt hatte ich mit der geschwungenen Klammer Testweise (anstatt der eckigen Klammer) keine Probleme mit dem ursprünglichen Syntax (geändert).
While [CC27].Value < 100
Musste aber über Taskmanager EXCEL abbrechen, da der PC ohne Unterlass rechnete. Womöglich erreichen die Berechnungen auch nie die 100.
Gruß Tron
Hi,
ich gebe es jetzt auf !
Bei allen vorgeschlagenen VBA´s, wenn ich den Syntax in die dafür vorgesehene Tabelle kopiert wurde wird dieser rot hervorgehoben.
Ausnahme sind der Sub Anfang und Ende und die Kommentartexte.
Hat mit VBA-Kennwortschutz sicherlich nichts zu tun ?!
Leider kann man hier keine Bilder einfügen.
Das ist meiner der mit 10 Neuberechnungen als Beispiel funktioniert (kleine Änderung bei der Auswahl und Ausführung).
Sub NeuBerechnen()
Calculate
Calculate
Calculate
Calculate
Calculate
Calculate
Calculate
Calculate
Calculate
Calculate
Range("BZ33:CI33").Select
Selection.Copy
Range("BZ40").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("BZ41").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
End Sub
Nö,
kein Zirkelbezug!
Manchmal muss man mit Zusatz- Spalten oder Zeilen arbeiten!
1000 Dank für Deine Rückmeldung :-)
Ist es Richtig, dass die Definierung <100 inkorrekt ist, müsste sicher >=100 sein ?
Ein Kopiervorgang, wie ich sehe, ist nicht definiert, ist aber auch Egal, wenn der Wert erreicht oder Überschritten wird, die Berechnung gestoppt wird.
Über Formel geht es nicht, da ich mit F9 "neu berechnen" durchführe, kann auch die ermittelte Daten kopieren, damit diese Bestand haben.
Bei jeder F9-Aktierung werden die Daten neu berechnet, können aber nicht Rückgängig gemacht werden, da es sich um Zahlen aus einem ZUFALLSBEREICH handelt!
Ich werde zum gegebenen Zeitpunkt das VBA Testen (ich gehe davon aus es in einem "Modul" definiert wird) und melde mich wieder.
Gruß Tron
Hi daCypher,
ich habe mein Anliegen etwas besser formuliert (26.01.2025).
Dein Syntax habe ich bisher nicht getestet, da kein Zellbezug zu CC27 vorliegt.
Gruß Tron