Formel als Standardwert, wenn Zelle leer

Forumthread: Formel als Standardwert, wenn Zelle leer

27.09.2020 23:07:49

Maverick

Hallo liebe Excel, respektive VBA-Profis.
Zu aller erst mal der Link zu meinem File: https://www.herber.de/bbs/user/140476.xlsm
Im Bereich VBA kenne ich mich leider noch überhaupt nicht aus, da ich bis anhin nur mit Formeln gearbeitet habe... nun stosse ich nur mit den Formeln aber an die Grenzen des machbaren und somit mit VBA leider auch an meine Grenzen...
Ich habe bereits viel online recherchiert, jedoch leider ohne Erfolg, da ich zwar ähnliches gefunden habe, dies aber nicht exakt auf mein Problem umschlüsseln kann. Ich hoffe nun, dass ihr mir helfen könnt.
Ausgangslage:
Ich mache einen Dienstplan für meine Mitarbeiter.
In Spalte B stehen die Namen der Mitarbeiter. (B4= erster Name / B19 = letzter Name) In der Zeile 3 steht das Datum (C3 = 01.01.2021 / NC3 = 31.12.2021) Wochenenden sowie Feiertage werden automatisch farblich markiert (bedingte Formatierung)
Im Bereich C4:NC19 können diverse Buchstaben erfasst werden. Bsp: F = Ferien, K = Krank, etc.
Ich will, dass wenn ich an einem Montag ein F erfasse, dies für die ganze Woche erfasst wird. Aktuell habe ich das mit folgender Formel gelöst:
Formel in D4: =WENNFEHLER(WENN(WENN(WOCHENTAG(D$3;2)>5;;C4)=0;;WENN(WOCHENTAG(D$3;2)>5;;C4));)br>
Problemstellung:
Wenn die einzelne Zelle überschrieben wird ist logischerweise die Formel weg und es gibt einen Unterbruch.
Ziel: Wenn eine Zelle überschrieben wird, soll dieser Wert drin stehen und in die folgenden Spalten übernommen werden, bis dort wo ein neuer Wert eingesetzt wird und ab dort soll dieser Wert übernommen werden. Wenn die Zelle aber mittels Delete geleert wird, soll default wieder die Formel eingesetzt werden.
Beispiel: Am Montag wird ein F erfasst. Dienstag bis und mit Freitag haben automatisch auch ein F. Am Mittwoch wird nun ein K erfasst. Montag und Dienstag = F, Mittwoch bis Freitag = K. Wenn jetzt das K am Mittwoch gelöscht wird, soll dort sowie am Donnerstag und Freitag wieder automatisch ein F erscheinen. Aktuell ist es aber so, dass Mittwoch bis Freitag dann leer bleiben.
Nebeninfo: Ich habe versucht mich mit VBA auseinanderzusetzen und folgenden Code geschrieben: pre>
Sub xyz() ThisWorkbook.Worksheets(Dienstplan AD).Activate Dim i As Integer For i = 4 To 367 If Weekday(Cells(3, i), vbMonday)

Hier ist allerdings das Problem, dass die Schleife sehr lange dauert, nur auf einer Zeile angewendet wird und immer manuell gestartet werden muss.
Ziel sollte sein, dass sobald eine Zelle geleert wird, automatisch die Formel wieder ergänzt wird.
Ich hoffe irgendjemand von euch kann mir helfen und ich bedanke mich jetzt schon herzlich :)
Beste Grüsse und hoffentlich bis bald
Maverick

2
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Formel als Standardwert, wenn Zelle leer

27.09.2020 23:46:53

MatthiasG
AW: Formel als Standardwert, wenn Zelle leer

28.09.2020 10:11:38

Maverick

AW: Formel als Standardwert, wenn Zelle leer

27.09.2020 23:46:53

MatthiasG

Hallo Maverik,
rechtsklick auf Blattregister "Dienstplan AD", "Code anzeigen" wählen.
Dann das hier reinkopieren:


Option Explicit
Const rng_Formula = "C4:NC19"
Const restore_Formula = "=IFERROR(IF(IF(WEEKDAY(R20C,2)>5,"""",RC[-1])=0,"""",IF(WEEKDAY(R20C,2) _
>5,"""",RC[-1])),"""")"
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng0 As Range, rng1 As Range
Set rng0 = Intersect(Target, Me.Range(rng_Formula))
If Not rng0 Is Nothing Then
If Target(1) = "" Then
Application.EnableEvents = False
Target.FormulaR1C1 = restore_Formula
'Fehler "ungeschützte Formel" abschalten:
For Each rng1 In Target
rng1.Errors.Item(6).Ignore = True
Next
Application.EnableEvents = True
End If
End If
End Sub

Allerdings kannst du dann z.B. einen Mittwoch nicht rauslöschen, wenn vorher ein Dienst steht. Es kommt ja die Formel rein und damit der Wert vom Montag.
Gruß Matthias

AW: Formel als Standardwert, wenn Zelle leer

28.09.2020 10:11:38

Maverick

Hallo Matthias
Wow... ich bin Sprachlos :O
Vielen herzlichen Dank für diese hammer Lösung, welche einwandfrei und ohne Verzögerungen läuft! Echt super!!
Beste Grüsse
Maverick

Infobox / Tutorial

Standardwert für Zellen in Excel festlegen

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um einen Standardwert für eine Zelle in Excel festzulegen, kannst du VBA (Visual Basic for Applications) verwenden. Hier ist eine einfache Anleitung, wie du dies umsetzt:

Öffne dein Excel-Dokument und gehe zum Arbeitsblatt, auf dem du den Standardwert festlegen möchtest.
Rechtsklicke auf das Blattregister und wähle „Code anzeigen“.
Füge den folgenden VBA-Code in das Codefenster ein:

Option Explicit
Const rng_Formula = "C4:NC19"
Const restore_Formula = "=IFERROR(IF(IF(WEEKDAY(R20C,2)>5,"""",RC[-1])=0,"""",IF(WEEKDAY(R20C,2)>5,"""",RC[-1])),"""")"

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng0 As Range, rng1 As Range
    Set rng0 = Intersect(Target, Me.Range(rng_Formula))
    If Not rng0 Is Nothing Then
        If Target(1) = "" Then
            Application.EnableEvents = False
            Target.FormulaR1C1 = restore_Formula
            ' Fehler "ungeschützte Formel" abschalten:
            For Each rng1 In Target
                rng1.Errors.Item(6).Ignore = True
            Next
            Application.EnableEvents = True
        End If
    End If
End Sub

Speichere deine Änderungen und schließe das VBA-Fenster.
Teste die Funktion, indem du eine Zelle in dem festgelegten Bereich leerst. Die Formel sollte automatisch wiederhergestellt werden.

Häufige Fehler und Lösungen

Fehler: Die Formel wird nicht wiederhergestellt.
- Lösung: Stelle sicher, dass der VBA-Code korrekt in das richtige Arbeitsblatt eingefügt wurde und dass die Range rng_Formula korrekt definiert ist.
Fehler: Die Formel wird überschrieben, wenn ich einen Wert eingebe.
- Lösung: Das Verhalten ist normal, da die Formel nur zurückkommt, wenn die Zelle gelöscht wird. Überlege, ob du das Verhalten der Anwendung anpassen möchtest.

Alternative Methoden

Wenn du keine VBA-Programmierung verwenden möchtest, kannst du auch folgende Methoden ausprobieren:

Datenüberprüfung: Nutze die Datenüberprüfung in Excel, um einen Dropdown-Standardwert festzulegen. Dies kann hilfreich sein, um sicherzustellen, dass nur bestimmte Werte eingegeben werden.
Vorformatiertes Template: Erstelle ein Excel-Template mit vordefinierten Formeln in den Zellen. Dies ist jedoch weniger dynamisch als die VBA-Lösung.

Praktische Beispiele

Angenommen, du möchtest in deinem Dienstplan einen Standardwert für Zellen, die leer bleiben, festlegen. Mit dem obigen Code kannst du sicherstellen, dass, wenn ein Mitarbeiter z.B. „K“ für krank eingibt und dann die Zelle löscht, die ursprüngliche Formel (und damit der Standardwert) wiederhergestellt wird.

Tipps für Profis

Verwendung von Named Ranges: Anstatt feste Zellbezüge zu verwenden, kannst du Named Ranges nutzen, um deinen Code flexibler zu gestalten.
Ereignisprozeduren optimieren: Achte darauf, dass du Application.EnableEvents = False nicht vergisst, um eine Endlosschleife zu vermeiden, wenn du die Formel zurücksetzt.

FAQ: Häufige Fragen

1. Wie kann ich den Standardwert für eine Zelle in Excel festlegen, ohne VBA zu verwenden?
Antwort: Eine VBA-freie Methode besteht darin, Datenüberprüfungen zu nutzen, um Auswahlmöglichkeiten zu bieten.

2. Funktioniert dieser Code in allen Excel-Versionen?
Antwort: Der VBA-Code ist kompatibel mit Excel 2007 und neueren Versionen. Achte darauf, dass Makros aktiviert sind.

3. Kann ich mehrere Standardwerte für verschiedene Zellen festlegen?
Antwort: Ja, du kannst mehrere Bereiche definieren und unterschiedliche Formeln für jeden Bereich im VBA-Code angeben.

4. Was passiert, wenn ich die Zelle manuell bearbeite?
Antwort: Sobald du einen Wert eingibst, wird die Formel überschrieben. Die Formel wird nur wiederhergestellt, wenn du die Zelle leer machst.

5. Wie kann ich die Performance des Codes verbessern?
Antwort: Vermeide unnötige Berechnungen, indem du den Bereich, in dem die Änderungen überwacht werden, so klein wie möglich hältst.

Beliebteste Forumthreads (12 Monate)

Formel als Standardwert, wenn Zelle leer

Forumthread: Formel als Standardwert, wenn Zelle leer

2 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Standardwert für Zellen in Excel festlegen

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

2
Beiträge zum Forumthread

Beiträge zu diesem Forumthread