Formel automatisch wieder herstellen nach manueller Eingabe

25.07.2025 14:38:27

Franco

Hallo

Wenn man in einer Zelle mit Formel eine manuelle Eingabe macht, wird die Formel gelöscht. Ich weiss, dass die Formel mit Ctrl+Z oder anderen manuellen Eingriffen wieder zurück geholt werden kann.

Kann man mit VBA einrichten, dass die Formel in der Zelle gilt, sofern kein manueller Eintrag gemacht wird?

In meinem konkreten Fall möchte ich eine Kalkulationsdatei erstellen, wo Brutto- und Nettopreise automatisch berechnet werden, wobei es offen sein soll, ob man den Brutto- oder den Nettopreis eingibt (Ich habe in den Optionen den Zirkularbezug erlaubt). Nun kann es vorkommen, dass man irrtümlich den falschen Preis in der falschen Spalte eintippt und dann korrigieren muss, bzw. dies erst später entdeckt. Da wäre es elegant, wenn die Formel wieder tragen würde, ohne alles manuell rückgängig machen zu müssen...

Vielen Dank für euren Input.

Franco

AW: Formel automatisch wieder herstellen nach manueller Eingabe

25.07.2025 15:55:20

Beverly

Hi Franco,

leider schreibst du nicht was für eine Formel du eintragen möchtest und in welcher Spalte dies passieren soll, aber vom prinzip her kann man das mit fogendem Code im Codemudul des Tabellenblattes lösen:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 2 Then '== Eingabe in Spalte B

        If Target = "" Then '== Zellinhalt wird entfernt

            Application.EnableEvents = False

            Target.Formula = "=" & Cells(Target.Row, 1).Address & "*" & Cells(Target.Row, 4).Address '==Eintrag der Formel

            Application.EnableEvents = True

        End If

    End If

End Sub

Wenn in Spalte B der Zellinhalt entfernt widr, wird stattdessen diese prinzipielle Formel eingetragen =$A$2*$D$2

Beachte, wenn mehrere Zellen markiert wurden, gibt es einen Laufzeitfehler - falls das nicht sein soll kann man den Code entsprechend anpassen.

Bis später
Karin

Link zur Homepage: https://excel-inn.de/

AW: Formel automatisch wieder herstellen nach manueller Eingabe

25.07.2025 17:25:30

Franco

Hallo Karin

Vielen Dank für deine prompte Antwort. Ich hatte vorhin eine Folgeantwort verfasst - aber die ist verschwunden. Ich versuche nochmal zu rekonstruieren:

Dein VBA-Code stimmt mich optimistisch, dass es eine Lösung geben könnte. Allerdings sind meine Formeln etwas komplizierter, vielleicht könntest du kurz beurteilen, ob sich diese Formeln mit den Bezügen grundsätzlich in VBA übersetzen lassen? ich erwarte keine pfannenfertige Lösung, eine Einschätzung würde schon helfen.

Hier eine Beispieldate der Kalkulation:
https://www.herber.de/bbs/user/178158.xlsx

Die iterative Berechnung habe ich aktiviert, da entweder Netto oder Brutto ausgefüllt wird.

Falls dies zu unsicher und fehleranfällig sein sollte, ziehe ich Daniels Weg in Betracht.

Vielen Dank euch allen.

LG Franco

AW: Formel automatisch wieder herstellen nach manueller Eingabe

25.07.2025 17:02:46

daniel

Hi

ich würde da mit 4 Zellen arbeiten.
zwei Eingabezellen und zwei Zellen mit Formel

in den Zellen mit den Formeln kommt dann prinzipiell so eine Formel:

Berechnung Brutto
Wenn Eingabe_Brutto und Eingabe_Netto leer, dann leer
Wenn Eingabe_Brutto_befüllt, übernimm Eingabe_Brutto, ansonsten Berechne Brutto aus Eingabe_Netto

Berechnung Netto:
Wenn Eingabe_Brutto und Eingabe_Netto leer dann leer
Wenn Eingabe_Netto befüllt, übernimm Eingabe_Netto, ansonsten Berechne Netto aus Eingabe Brutto

Gruß Daniel

AW: Formel automatisch wieder herstellen nach manueller Eingabe

25.07.2025 17:34:41

Yal

Hallo Franco,

am einfachste ist es, wenn Netto nur Eingabe und Brutto nur Formeln beinhalten.

Auf dem Brutto (nur Formeln) legst Du eine Worksheet_Change-Ereignis, das die Formel wieder herstellt und der entprechende Netto rechnet und ablegst.
Zur Vereinfachung, benenne die Spalte mit Netto-Werte "Netto" und die Spalte mit Brutto-Formeln "Brutto".

Dann sollte folgende Code passen:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngNetto As Range

Dim rngBrutto As Range

Dim ZielWert



    If Not Intersect(Target, Range("Brutto")) Is Null Then 'nur wenn Brutto geändert. ("Netto" ist Eingabe)

        Application.EnableEvents = False

        Set rngNetto = Intersect(rngBrutto.EntireRow, Range("Netto"))

        ZielWert = rngBrutto.Value 'Werte speichern

        Application.Undo 'Eingabe zurücknehmen, Formel ist wieder drin

        rngBrutto.GoalSeek Goal:=ZielWert, ChangingCell:=rngNetto 'Netto durch Zielwertsuche einstellen

        Application.EnableEvents = True

    End If

End Sub

VG
Yal

AW: Formel automatisch wieder herstellen nach manueller Eingabe

25.07.2025 18:41:39

daniel

wie oft änderst du die Liste?

ich würde es so machen:

1. füge oberhalb erst nochmal zwei Leerzeilen ein
2. in die erste Zeile kommt in die Spalten, die in der Tabelle eine berechnungsformel erhalten sollen, die jeweilige Formel, quasi als Speicher.
von dort kannst du sie dann immer bei bedarf in die Tabelle kopieren.

leer dann alle Brutto und Netto-Preise und trage dann die jeweils bekannten Preise in die richtige Spalte ein

dann kopierst du die Formel aus der Zeile 1 und fügst sie in der Tabelle in der Spalte ein, dazu selektierst du die ganze Spalte und schränkst dann die Auswahl auf die Leerzellen ein, damit die bekannten Preise nicht überschreiben werden (Start - Bearbeiten - Suchen und Auswählen - Inhalte - Leerzellen)
das kannst du dir dann auch als Marko programmieren (der Recorder ist dein Freund) und dieses Makro dann auf Anforderung ausführen (automatisch halte ich nicht für gut)

entscheiden ist, dass du die Formel in einer Zelle hinterlegst und sie von dort kopierst, dann musst du dich nicht darum kümmern, wie die Formel in VBA geschrieben werden muss und wenn du die Formel ändern willst, musst du den Code nicht ändern.

Gruß Daniel

AW: Formel automatisch wieder herstellen nach manueller Eingabe

25.07.2025 19:12:14

Franco

Vielen Dank euch allen für eure prompten Inputs, Einschätzungen und Hilfen. Ihr habt mir sehr geholfen.
Ich bin neu als registrierter User in diesem Forum und schätze euren Einsatz sehr. Ich hoffe, ich kann mich bei anderen Themen auch helfend einbringen.

LG Franco

AW: Formel automatisch wieder herstellen nach manueller Eingabe

25.07.2025 19:00:51

Franco

Danke, Daniel.

Die Liste ist nicht für mich selbst sondern für Benutzer gedacht, die möglichst selbsterklärend die Liste anwenden sollen. Ich denke, dein 4-Zellen-Modell macht in diesem Fall am meisten Sinn. Die Idee mit den griffbereiten Formeln und Makros finde ich toll, das werde ich mir sicher für ein anderes Mal merken.

LG Franco

Übrigens

25.07.2025 20:17:52

Yal

Das "4-Zellen-Modell" lässt sich, insbesondere wenn die Eingabe alle auf einmal erfolgen oder aus einer Quelle kommen, sehr gut mit Power Query kombinieren.
Power Query (PQ) wäre besonders geeignet, wenn die Daten in csv, Excel oder PDF geliefert werden.

Warum?
Mit PQ wird eine Transformation eingerichtet, ungefähr wie mit dem Makrorekorder, aber ohne VBA. Diese Transformation ist spaltenorientiert eingerichtet. Der if..then..else.. wird mit temporäre Hilfespalte, die vor der Ausgabe wieder entfernt werden.

Tutorial: https://excelhero.de/pivot-tabellen/pivot-tabellen-ganz-einfach-erklaert/

VG
Yal

AW: Formel automatisch wieder herstellen nach manueller Eingabe

25.07.2025 17:58:29

Franco

Hallo Yal

Vielen Dank für deine Überlegung. Es sollte halt schon sein, dass der User Netto- und Bruttobeträge erfassen kann, weil Preislisten je nach Lieferant brutto oder netto sind und erst zum Schluss berechnet werden.

Aber wenn ich deine Formel (mit meinen schwachen VBA-Kenntnissen) verstehe, könnte ich diese auf beide Spalten anwenden, unabhängig, ob ich eine Formel berechnen lasse oder manuelle Werte erfasse? oder geht das nicht?

LG Franco

AW: Formel automatisch wieder herstellen nach manueller Eingabe

25.07.2025 18:23:00

Yal

Hallo Franco,

genau.
Es sind 2 Spalten. Diese werden in "Netto" und "Brutto" benannt (Menü "Formel", "Namensmanager"). Um die Excel-Regel festzuhalten, dass eine Zelle entweder eine Formel oder einen eingegebene Wert beinhaltet, benutzen wir den Trick, dass wenn die Brutto-Formel überschrieben wird,
- der eingegebene Wert gespeichert wird,
- die Formel restauriert wird ("Undo". Es ist daher -fast- egal, um welche Formel es sich handelt) und
- der entsprechende Netto-Wert in der passenden Netto-Zelle (gleiche Zeile aber andere Spalte) durch die sog. Zielwert-Suche (GoalSeek) eingetragen wird.
- Daraus wird der Brutto berechnet, weil im Brutto-Zelle die Formel wieder am Platz ist.

Für den User ist diese Hintergrund-Jongliererei nicht sichtbar.

"Formel fast egal": aus Brutto = Netto / 1,19 ist es keine Problem, eine Zielwertsuche zu lösen. Bei komplizierten Formeln wie z.B. SVerweis ist es unmöglich.

VG
Yal

AW: Formel automatisch wieder herstellen nach manueller Eingabe

25.07.2025 18:53:06

holobiont

Hallo Yal
Interessanter Ansatz. Ich werde mir dies für künftige Projekte merken und speichern. Der Hinweis zu den unmöglichen Formeln ist sehr wertvoll, danke. In vorliegendem Projekt verwende ich Dropdownlisten mit Bezug auf andere Register unter Anwendung von SVerweis.... Also lasse ich's in diesem Fall lieber bleiben mit VBA und wende Daniels 4-Zellen-Modell an.
LG Franco

AW: Formel automatisch wieder herstellen nach manueller Eingabe

25.07.2025 17:07:41

Franco

Hallo Daniel

Vielen Dank für deinen Input. Das wäre natürlich auch ein Weg und weniger fehleranfällig. Falls ich am VBA-Weg scheitere, nehme ich das gerne an. Ich bin froh, dass soviele Wege nach Rom führen.

LG Franco

AW: Formel automatisch wieder herstellen nach manueller Eingabe

25.07.2025 18:58:54

schauan

Hallo Franco,

ich wäre auch bei daniels Ansatz. Abgewandelt könnte man auch in eine Spalte den Wert eintragen und in eine zweite Spalte nur die Auswahl B oder N für Brutto oder Netto. Danach kannst Du dann die Zielzellen berechnen. Hier würde schon die Änderung der Auswahl reichen, der Wert müsste nicht nochmal eingegeben werden :-)
Man könnte natürlich auch nur ein Kennzeichen eintragen, z.B. N, und wenn nix drin steht, dann ist es Brutto.

Wenn Du den Anwender verwirren willst und keine negativen Beträge kommen, könntet Du z.B. auch Netto als negativen Betrag eingeben und Brutto als positiven.
Da gibt es beim Zellformat eine Einstellung, wo negative Werte ohne Vorzeichen in rot dargestellt werden. Man sieht also das Minus nicht, aber farblich gleich den Unterschied Brutto/Netto.

AW: Formel automatisch wieder herstellen nach manueller Eingabe

25.07.2025 19:08:12

Franco

Gute Ergänzung, danke. Ja, ich bin dank Karins und Yals Einschätzungen zur VBA-Umsetzung auch auf Daniels Linie. Die Idee mit dem Switch N/B (oder defalt netto und mit Checkbox = brutto?...) gefällt mir. So werde ich es machen.

LG Franco

Formel automatisch wieder herstellen nach manueller Eingabe

Forumthread: Formel automatisch wieder herstellen nach manueller Eingabe

14
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Formel automatisch wieder herstellen nach manueller Eingabe

Forumthread: Formel automatisch wieder herstellen nach manueller Eingabe

14 Beiträge zum Forumthread Beiträge zu diesem Forumthread

14
Beiträge zum Forumthread

Beiträge zu diesem Forumthread