Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Gelöschten Zellwert durch Formel ersetzen


Betrifft: Gelöschten Zellwert durch Formel ersetzen von: Werner Wassermann
Geschrieben am: 12.09.2019 10:43:58

Moin!
Ich habe eine Excel-Tabelle erstellt, bei der sich je nach manueller Eingabe in der ersten Spalte in den übrigen Spalten jeweils etwaige Überschriften, Formeln und Formatierungen automatisch erstellen.
Damit passt sich die Tabelle in Aussehen und Funktion den Eingaben in der ersten Spalte an. Soweit, so gut.
In den durch diese Vorgaben ohne Inhalt angezeigten Zellen werden in der Folge Eingaben manuell vorgenommen, die dann in anderen Zellen zu entsprechenden Berechnungen führen usw.

Das Problem dabei ist, dass eine manuelle Eingabe die vorher in dieser Zelle vorhandene Formel unwiederbringlich löscht. Wenn ich also alle gemachten Eingaben lösche, habe ich nicht die ursprüngliche Tabelle wieder hergestellt, die sich bei Eingaben in der ersten Spalte vollständig selbst formatiert. Genau diesen Effekt möchte ich aber dringend haben!

Meine Frage daher: Gibt es eine Möglichkeit, eine Formel so an einer Zelle zu verankern, dass sie zwar durch eine manuelle Eingabe zeitweise überschrieben wird, bei der Löschung des Zellinhaltes aber wieder die ursprüngliche Formel aktiv wird?

Oder muss ich das Thema eventuell von vornherein ganz anders andenken?

Ich habe bei der Thematik sogar Schwierigkeiten, mir passende Suchbegriffe auszudenken, mit denen ich hier im Forum oder sonst im Internet nach Tipps suchen könnte, daher wende ich mich mit der Frage an die Allgemeinheit.
Ich hoffe, ich konnte verständlich machen, was ich möchte und wo der Schuh drückt.
Weiß jemand Rat?

Gruß
Werner

  

Betrifft: AW: Gelöschten Zellwert durch Formel ersetzen von: 1712641.html
Geschrieben am: 12.09.2019 10:56:21

Hi Werner,

eine Formel kann man nicht "verankern" - wenn man sie mit einem fixen Wert überschreibt dann ist sie definitiv weg. Aber man könnte das mit VBA lösen, indem man z.B. das Worksheet_Change-Ereignis benutzt um eine Formel wieder einzutragen, wenn der Zellinhalt gelöscht wird. Du musst dann aber im Code genau vorgeben, welche Formel in welche Zelle eingetragen werden soll.


GrußformelBeverly's Excel - Inn
  

Betrifft: AW: 2. Sheet von: 1712642.html
Geschrieben am: 12.09.2019 10:57:14

Hallo,

ein "Workaround" könnte sein, die Formeln auf einem anderen, ausgeblendeten Blatt zu speichern. Dann können sie beim Löschen einer Zelle zurück kopiert werden.

mfg

  

Betrifft: AW: Gelöschten Zellwert durch Formel ersetzen von: 1712643.html
Geschrieben am: 12.09.2019 10:58:04

Hallo Namensvetter

Oder muss ich das Thema eventuell von vornherein ganz anders andenken?
Ja!
Alle Formelzellen schützen
Du kannst z.B. mit Hilfsspalten abrbeiten.
Zelle B2 steht eine Formel, Zelle C2 ist für manuelle Eingaben, D2 =wenn(d2="";b2;d2)
etc.

Gruss Werni

  

Betrifft: AW: Gelöschten Zellwert durch Formel ersetzen von: 1712848.html
Geschrieben am: 13.09.2019 11:04:22

Hallo Tippgeber, hallo Namensvetter,
erstmal vielen Dank für die prompten Anworten!
Die Methode von Werni hatte ich auch bereits näher betrachtet und ausprobiert, dann aber wieder verworfen. Mir gefällt dabei einfach nicht, dass ich für die Eingabe eine andere Zelle benutze, als diejenige, in der nachher der Inhalt betrachtet wird. Das finde ich wenig intuitiv.

Über den Hinweis von Beverly mit der VBA-Umgebung habe ich mittlerweile eine Lösung gefunden. Ich habe zwar von VBA nahezu keine Ahnung, aber ich habe es immerhin hinbekommen, den Code, den ich dazu an anderer Stelle gefunden habe, in meine Excel-Datei einzubauen und es läuft.

Das geht jetzt so: Nach Fertigstellung meiner Tabelle in Rohform muss ich dieses Modul aufrufen, das dann sämtliche in der Tabelle vorgefundenen Formeln in den Kommentar der Zelle kopiert:

Sub MerkeFormeln()
Dim Benutzt, z As Range
Set Benutzt = Range(Cells(1, 1), Cells.SpecialCells(xlCellTypeLastCell))
For Each z In Benutzt
If z.HasFormula Then
z.NoteText Text:=z.Formula
Else
z.NoteText Text:=""
End If
Next z
End Sub
In dem Tabellenblatt selbst sorgt dann diese Prozedur dafür, dass bei Löschung des Zellinhaltes stattdessen der Inhalt des Kommentares in die Zelle kopiert wird:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim z As Range
Application.EnableEvents = False
For Each z In Target
    If z.Value = "" Then
        z.Value = z.NoteText
        'z.Calculate
    End If
    If z.HasFormula Then
        z.NoteText Text:=z.Formula
        z.Font.ColorIndex = 3
    Else
        z.Font.ColorIndex = xlColorIndexAutomatic
    End If
Next z
Application.EnableEvents = True
End Sub
Dass dabei der Inhalt der Zelle, der nicht von Hand eingegeben wurde, rot dargestellt wird, habe ich anschließend noch wieder in schwarz geändert und so läuft es.

Was mich aber dabei stört:
Jetzt sind naturgemäß alle Zellen, die eine Formel enthalten, mit einem Kommentar versehen. Ich kann also keine manuellen Kommentare mehr vergeben (was ich durchaus gerne weiterhin täte!) und außerdem ist die ganze Tabelle voller entsprechender Hinweis-Ecken. Das sieht blöde aus.

Ich würde also gerne den Tipp von Fennek umsetzen: statt die Formeln in den Kommentar zu kopieren, sie an die jeweils gleiche Stelle eines leeren Tabellenblattes kopieren und von dort zurückholen. Da ich aber absoluter VBA-Neuling bin, weiß ich nicht, wie ich das in eine entsprechende Änderung des Codes oben umsetzen kann.

Kann mir da jemand auf die Sprünge helfen? Das wäre toll!

Gruß
Werner
  

Betrifft: AW: Gelöschten Zellwert durch Formel ersetzen von: 1712853.html
Geschrieben am: 13.09.2019 11:18:12

Hallo Tippgeber, hallo Namensvetter,
erstmal vielen Dank für die prompten Anworten!
Die Methode von Werni hatte ich auch bereits näher betrachtet und ausprobiert, dann aber wieder verworfen. Mir gefällt dabei einfach nicht, dass ich für die Eingabe eine andere Zelle benutze, als diejenige, in der nachher der Inhalt betrachtet wird. Das finde ich wenig intuitiv.

Über den Hinweis von Beverly mit der VBA-Umgebung habe ich mittlerweile eine Lösung gefunden. Ich habe zwar von VBA nahezu keine Ahnung, aber ich habe es immerhin hinbekommen, den Code, den ich dazu an anderer Stelle gefunden habe, in meine Excel-Datei einzubauen und es läuft.

Das geht jetzt so: Nach Fertigstellung meiner Tabelle in Rohform muss ich dieses Modul aufrufen, das dann sämtliche in der Tabelle vorgefundenen Formeln in den Kommentar der Zelle kopiert:

Sub MerkeFormeln()
Dim Benutzt, z As Range
Set Benutzt = Range(Cells(1, 1), Cells.SpecialCells(xlCellTypeLastCell))
For Each z In Benutzt
If z.HasFormula Then
z.NoteText Text:=z.Formula
Else
z.NoteText Text:=""
End If
Next z
End Sub
In dem Tabellenblatt selbst sorgt dann diese Prozedur dafür, dass bei Löschung des Zellinhaltes stattdessen der Inhalt des Kommentares in die Zelle kopiert wird:

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim z As Range
 Application.EnableEvents = False
 For Each z In Target
     If z.Value = "" Then
         z.Value = z.NoteText
         'z.Calculate
     End If
     If z.HasFormula Then
         z.NoteText Text:=z.Formula
         z.Font.ColorIndex = 3
     Else
         z.Font.ColorIndex = xlColorIndexAutomatic
     End If
 Next z
 Application.EnableEvents = True
 End Sub
Dass dabei der Inhalt der Zelle, der nicht von Hand eingegeben wurde, rot dargestellt wird, habe ich anschließend noch wieder in schwarz geändert und so läuft es.

Was mich aber dabei stört:
Jetzt sind naturgemäß alle Zellen, die eine Formel enthalten, mit einem Kommentar versehen. Ich kann also keine manuellen Kommentare mehr vergeben (was ich durchaus gerne weiterhin täte!) und außerdem ist die ganze Tabelle voller entsprechender Hinweis-Ecken. Das sieht blöde aus.

Ich würde also gerne den Tipp von Fennek umsetzen: statt die Formeln in den Kommentar zu kopieren, sie an die jeweils gleiche Stelle eines leeren Tabellenblattes kopieren und von dort zurückholen. Da ich aber absoluter VBA-Neuling bin, weiß ich nicht, wie ich das in eine entsprechende Änderung des Codes oben umsetzen kann.

Kann mir da jemand auf die Sprünge helfen? Das wäre toll!

Gruß
Werner
  

Betrifft: AW: Gelöschten Zellwert durch Formel ersetzen von: 1712925.html
Geschrieben am: 13.09.2019 14:24:36

HI

z.b. statt z.NoteText dann Sheets("anderes_Blatt").Range(z.Address).Formula


Gruß Daniel

  

Betrifft: AW: Gelöschten Zellwert durch Formel ersetzen von: 1713099.html
Geschrieben am: 14.09.2019 18:45:14

Hallo Daniel,
vielen Dank erstmal für die prompte Antwort!
Ich habe das ganz stur so gemacht, wie Du geschrieben hast, also jeweils z.NoteText durch Sheets(5).Range(z.Address).Formula ersetzt.

Meine Original-Tabelle mit den Zellen mit wechselndem Inhalt (Formel/freie Eingabe) ist sheet 2, die leere Tabelle, in der die Formeln zwischengelagert werden sollen, ist sheet 5.

Der code, der als Aufforderung zum Speichern der Formeln aufgerufen wird, lautet nun:

Sub MerkeFormeln()
Dim Benutzt, z As Range
Set Benutzt = Range(Cells(1, 1), Cells.SpecialCells(xlCellTypeLastCell))
For Each z In Benutzt
If z.HasFormula Then
Sheets(5).Range(z.Address).formula Text:=z.Formula
Else
Sheets(5).Range(z.Address).formula Text:=""
End If
Next z
End Sub

Das tut auch, was es soll, allerdings offensichtlich in einer Endlosschleife. Wenn ich das per Task-Manager abbreche, dann habe ich das gewünschte Ergebnis, aber die Vorgehensweise ist natürlich nicht Sinn der Sache... ;-)

Irgendwas ist an dem Code also faul und da ich zum ersten Mal mit VBA in Kontakt bin, sehe ich mich nicht imstande, das herauszufinden geschweige denn zu beheben.
Kann jemand helfen?

Weiter geht es dann mit dem Code zum Zurücksichern der Formeln, der ziemlich sicher das gleiche Problem enthalten wird.
Nach simplem Ersetzen, wie von Daniel angeregt, sieht der Code nun so aus:

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim z As Range
 Application.EnableEvents = False
 For Each z In Target
     If z.Value = "" Then
         z.Value = Sheets(5).Range(z.Address).Formula
         'z.Calculate
     End If
     If z.HasFormula Then
         Sheets(2).Range(z.Address).Formula:=z.Formula
     End If
 Next z
 Application.EnableEvents = True
 End Sub
Wobei ich da nicht verstehe, was die zweite if-Schleife eigentlich tut und ich von daher auch nicht sicher bin, dass dort sheets(2) hineinkommt. Kann mir da jemand auf die Sprünge helfen?

Vielen Dank!

Werner
  

Betrifft: AW: Gelöschten Zellwert durch Formel ersetzen von: 1713107.html
Geschrieben am: 14.09.2019 19:11:55

Hi
ich sehe nicht, wo der erste Code in eine Endlosschleife laufen sollte.
du machst das nur umständlich weil du wirklich jede Zelle beschreibst, auch die Zellen ohne Formel und sogar leerzellen, dauert das ne Weile.

wenn du schon SpecialCells kennst und benutzt, dann kannst du das auch verwenden, um die Schleife nur über die Formeln laufen zu lassen. Vorher löscht man dann das Blatt:

Sheets(5).Cells.Clear
For each Z .Cells.SpecialCells(xlcelltypeformulas)
    Sheets(5).Range(z.Address).formula Text:=z.Formula
Next

oder du kopierst alles in einem Rutsch und löscht dann das nicht benötigte raus:
Sheets(2).Cells.Copy
Sheets(5).Cells.PasteSpecial xlpasteformulas
Sheets(5).Cells.SpecialCells(xlcelltypeconstants).Clearcontents

Gruß Daniel
  

Betrifft: AW: Gelöschten Zellwert durch Formel ersetzen von: 1713135.html
Geschrieben am: 14.09.2019 21:40:21

Daniel, Du traust mir eindeutig zu viel zu...

Ich setze brav wie ein Schaf Deine Zeile ein und werde mit Fehlermeldungen bombardiert.

Ich habe den Code jetzt zu diesem verändert, aber das läuft immer noch nicht:

Sub MerkeFormeln()
 Dim Benutzt, z As Range
 Sheets(5).Cells.Clear
 Set Benutzt = Range(Cells(1, 1), Cells.SpecialCells(xlCellTypeFormulas))
 For Each z In Benutzt
     Sheets(5).Range(z.Address).Formula Text:=z.Formula
 Next z
 End Sub

Nch meinem Verständnis sollte doch jetzt schlicht zu Beginn das Sheet5 gelöscht werden, um es dann beschränkt auf die Zellen mit Formeln neu zu befüllen, korrekt?
Ich bekomme aber immer einen Laufzeitfehler in der Zeile mit Sheets(5).Cells.Clear.
Lasse ich die Zeile weg, dann bekomme ich stattdessen den Fehler in der anderen Zeile, die mit Sheets(5) beginnt.

Hier ist also noch irgendetwas faul.

Ich kämpfe mich da redlich durch, aber ich fürchte, ich brauche einfach diese Zeilen Code in einer lauffähigen Form, denn es gibt einfach viel zu viele Fettnäpfchen, in die man als blutiger VBA-Laie da treten kann.

Dasselbe gilt für den Code für das Zurückschreiben (siehe weiter vorne).

*seufz*

Werner
  

Betrifft: AW: Gelöschten Zellwert durch Formel ersetzen von: 1713378.html
Geschrieben am: 16.09.2019 23:24:49

Hallo Werner

ich biete mal zwei Codes an zum Formeln kopieren in Sheet5 und zum zurückschreiben in Sheet2.
Beim kopieren in Sheet5 habe ich noch eine On Error Routine eingebaut, falls mal kein Formeln gefunden werden.
Beim zurückkopieren wird die Formel nur in die leeren Zellen kopiert. Da wo feste Werte drin stehen bleibt der Wert erhalten!

Ein Fehler in deinem Code war m.E. die fehlende exakte Zuweisung von Sheet2 + Sheet5 vor Range SpecialCells xlFormulas.

mfg Piet

Sub MerkeFormeln()
Dim Benutzt, z As Range
On Error Resume Next
Set Benutzt = Sheets(2).Cells.SpecialCells(xlCellTypeFormulas)
If Err > 0 Then MsgBox "Keine Formeln im Blatt 2": Exit Sub
Sheets(5).Cells.Delete
For Each z In Benutzt
    Sheets(5).Range(z.Address).Formula = z.Formula
Next z
End Sub


Sub FormelnRückschreiben()
Dim Benutzt, z As Range
On Error Resume Next
Set Benutzt = Sheets(5).Cells.SpecialCells(xlCellTypeFormulas)
If Err > 0 Then MsgBox "Keine Formeln im Blatt 5": Exit Sub
For Each z In Benutzt
    If Sheets(2).Range(z.Address).Formula = Empty Then
       Sheets(2).Range(z.Address).Formula = z.Formula
    End If
Next z
End Sub

  

Betrifft: AW: Gelöschten Zellwert durch Formel ersetzen von: 1713137.html
Geschrieben am: 14.09.2019 21:53:47

Ich suche immer noch nach dem Fehler bei der Ersetzung der Zeilen und finde ihn partout nicht. langsam kriege ich graue Haare darüber... :-[

  

Betrifft: AW: Gelöschten Zellwert durch Formel ersetzen von: 1713381.html
Geschrieben am: 16.09.2019 23:52:20

Hallo Werner

ich habe noch einen dritten Code im Angebot der dir noch besser gefallen wird. Besonders bei vielen Daten.
Beim löschen einer Zelle per Target Makro prüfen ob dort eine Formel existiert und sie sofort hinein kopieren. Was sagst du dazu??

Der Code gehört aber nicht in ein Modul, sondern ins Code Blatt für Sheet2. Dazu im Editor Sheet2 ankliicken und den Code hinein kopieren.
Der sollte sofort laufen. Er setzt aber voraus das nur in einer Zelle auf Formel geprüft wird, nicht einen ganzen Bereich von mehreren Zellen!

mfg Piet

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Fehler
If Target.Count > 1 Then Exit Sub
If Target.Value <> Empty Then Exit Sub

If Sheets(5).Range(Target.Address).HasFormula Then
   Target.Formula = Sheets(5).Range(Target.Address).Formula
End If
Fehler:
End Sub

Beiträge aus dem Excel-Forum zum Thema "Gelöschten Zellwert durch Formel ersetzen"