Zeilen überschreiben ohne Formel zu löschen

Forumthread: Zeilen überschreiben ohne Formel zu löschen

18.07.2013 16:54:44

Peter

Hallo Liebe Experten,
ich stehe vor einem Problem in der VBA Programmierung und würde mich sehr freuen wenn sich jemand erbarmt und einen Vorschlag zur Lösung hat.
Was wir haben:
Eine Liste: G718:G797 (in jeder Zelle ist der Bezug zu einem anderen Tabellenblatt "Nutzungsdauern $B$19", also eine "Formel")
In diese Liste soll also immer das Rein was in der einen Zelle B19 steht.
Allerdings soll die Überschreibung in einzelnen Zellen innerhalb der Liste möglich sein ohne aber den Bezug zu B19 zu verlieren, bzw. wenn ich etwas rein schreibe und es danach wieder lösche, wieder der Bezug zu B19 da ist.
Ich habe ein Makro geschrieben, der aber nicht funktioniert.
Hier der Makro:


Sub Anlagen(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("G718:G797")) Is Nothing Then
Target = Sheets("Nutzungsdauern").Cells(19, 2)
Cancel = True
End If
End Sub

Dazu kommt: Dieser Makro soll auch für andere Listen erstellt werden, dann aber mit einer anderen Bezugszelle aus dem TB Nutzungsdauern... aber eins nach dem anderen
Vielen Dank im Voraus.
Viele Grüße
Peter

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Zeilen überschreiben ohne Formel zu löschen

18.07.2013 17:48:10

Hajo_Zi

Hallo Peter,
schaue mal hier http://www.ms-office-forum.net/forum/showthread.php?t=301493

AW: Zeilen überschreiben ohne Formel zu löschen

19.07.2013 09:02:28

Peter

Vielen Dank für die schnelle Antwort.
Allerdings gibt mir die Diskussion, auf die du mich verwiesen hast, nicht wirklich neue Erkenntnisse.
Würdest du sagen das mein Makro richtig aussieht? Oder fallen die vlt. direkt Fehler auf?
Einen schönen Tag wünsche ich!
Peter

Falls du mit dem verlinkten typischen ...

18.07.2013 19:00:47

Luc:-?

…„Hajo-AW-Thread“ nicht klar kommen solltest, Peter,
haben wir auch hier im Archiv einiges zu bieten.
Damit dein Anliegen sich automatisch regelt, musst du eine EreignisProzedur des Blattes anlegen, also auf dem DokumentKlassenModul des betroffenen Blattes (quasi seiner verborgenen VBA-Rückseite). Dazu doppelklickst du im VBEditor auf die Tabelle, damit deren Seite bereitsteht. Über ihr befinden sich 2 Auswahlfelder; im linken wählst du Worksheet aus. Dadurch wird hier gleich Kopf- und Endzeile der EreignisProzedur Worksheet_SelectionChange angelegt. Die brauchst du zwar nicht, aber die betreffenden Zeilen der benötigten sehen genauso aus. Du brauchst also nur das Selection aus dem Kopf zu entfernen. In die leere Zeile dazwischen setzt du anschließend folgenden Code:

    Const adRelBer$ = "G718:G797"
Const BlattListe$ = "Nutzungsdauern!Blatt2!Blatt3!…BlattX" 'BlattTitel in ZeilenRhf!
Dim bix As Long, axBlaLi As Variant, relBer As Range, Ziel As Range
On Error GoTo ex
Set relBer = Intersect(Target, Range(adRelBer))
If Not relBer Is Nothing Then
Application.EnableEvents = False
axBlaLi = Split(BlattListe, "!")
bix = relBer.Cells(1).Row - Range(adRelBer).Cells(1).Row
For Each Ziel In relBer
If Ziel = "" Then Ziel.Formula = "='" & axBlaLi(bix) & "'!$B$19"
bix = bix + 1
Next Ziel
ex:     Application.EnableEvents = True
Set relBer = Nothing
End If

So, das war mal aus dem Stegreif, testen darfst du! ;-)
Gruß Luc :-?

AW: Falls du mit dem verlinkten typischen ...

19.07.2013 11:52:17

Peter

Super!Vielen Dank Luc!
Ich bin neu im Forum und habe deine Antwort erst garnicht gesehen.
Deinen Makro habe ich ein wenig modifiziert (Zeile 10 gelöscht) und nun funktioniert er.
Jetzt kommt aber ein weiteres Problem: Der Wert der mir angezeigt wird wenn ich ihn per Hand eintrage darf nicht unter dem liegen den ich aus $B$19 bekomme bzw. bekommen würde. Wäre also mit einer WENN Funktion zu vergleichen z.B
WENN(UND(G718:G797=Nutzungsdauer$B$19(bzw.per Hand eingetragen);G718:G797 größer Nutzungsdauer$B$19-1);per Hand eingetragen;FALSCH))
Ich habe versuch die WENN Funktion bzw. eine solche Bedingung in den Makro einzubauen, bis jetzt aber ohne Erfolg.
Hast du vielleicht eine Idee?
Vielen Dank.
Viele Grüße
Peter
p.s.
VBA macht langsam Spaß :P

Komme frühestens abends dazu! orT

19.07.2013 15:44:59

Luc:-?

Gruß Luc :-?

Na, dann wieder einer mehr, ...

20.07.2013 01:45:57

Luc:-?

…Peter… ;-)
So, nun habe ich die EventProc doch noch selbst getestet und siehe da, sie fktioniert auf Anhieb, allerdings mit allen Zeilen und entsprd Anpassung an meine Testdatei. Die „ominöse“ Zeile 10 sollte bei richtiger Zählung die sein, die mit bix = anfängt. Die ist aber unerlässlich, um den jeweiligen Anfangsindex der Änderung festzustellen. Man kann nämlich auch mehrere Zellen gleichzeitig löschen und dann umfasst Target alle diese Zellen. Allerdings sollten die auch zusammenhängen, sonst könnte es Probleme geben.
Der neue Code sieht komplett bei mir unter Xl12 so aus:

Private Sub Worksheet_Change(ByVal Target As Range)
Const adRelBer$ = "G718:G797", adQBer = "$B$19"    '"A11:A18"
Const BlattListe$ = "Nutzungsdauern!Blatt2!Blatt3!Blatt5!Blatt4!Blatt9!Blatt7!" & _
"Blatt8!BlattX!BlattY!BlattZ"    'BlattTitel in ZeilenRhf!
Dim bix As Long, axBlaLi As Variant, relBer As Range, Ziel As Range
On Error GoTo ex
Set relBer = Intersect(Target, Range(adRelBer))
If Not relBer Is Nothing Then
Application.EnableEvents = False
axBlaLi = Split(BlattListe, "!")
bix = relBer.Cells(1).Row - Range(adRelBer).Cells(1).Row
For Each Ziel In relBer
If Ziel = "" Or Ziel

Falls sich mal die Bereiche ändern, musst du idR nur die Konstanten am PgmAnfang entsprd korrigieren. Die Einträge in der BlattListe müssen in der Reihenfolge ihrer Benutzung in den Verweisen der Spalte G erfolgen. Wird ein Blatt mehrmals benötigt, muss es auch mehrmals eingetragen wdn. Wird die Liste zu lang, kann man sie wie jetzt oben gezeigt trennen. Ich habe zum Testen die oben auskommentierte Bereichsangabe und flgd BlattListe benutzt:
"Tabelle2!Tabelle4!Tabelle9!Tabelle5!Tabelle6!Tabelle1!Tabelle8!Tabelle7"
Daran kann man erkennen, dass nicht die Reihenfolge der Blätter in der Mappe wichtig ist, sondern die Reihenfolge, in der sie in den Zellen benötigt wdn!
Übrigens würde es auch reichen, If Ziel zu schreiben, aber dann kann uU auch ="" bzw nur ' in eine relevante Zelle eingetragen wdn ohne dass das korrigiert wird! Falls dir das sogar recht wäre, kannst du das Pgm ja entsprd ändern. Bisher passiert das nur, falls ein Text eingetragen wird, aber das kann man auch abfangen, indem man statt Ziel = "" ⇒ Not IsNumeric(Ziel) schreibt.
Falls beliebige Mehrfach-ZellAuswahl-Löschungen (also auch unzusammenhängende) fehlerlos möglich sein sollen, muss das Pgm entsprd geändert wdn (könnte ich bei Bedarf noch machen).
Achtung! Das alles fktioniert nur bei manueller Änderung oder Einkopieren neuer Werte!
Gruß + schöWE, Luc :-?
PS: Noch ein Hinweis; guter PgmierStil ist es, eine EventProc nicht mit speziellen Pgmm zu überladen. Wenn sie mehrfach genutzt wdn soll, ist es besser, sie wie einen Verteiler zu benutzen, d.h., unter bestimmten GrundBedingg wird eine entsprd Subroutine (sog Makro) aufgerufen (ggf auch parametriert!), die entweder als Private ebenfalls im selben oder Public in einem allgemeinen Modul steht.
Besser informiert mit …

Infobox / Tutorial

Zeilen überschreiben ohne Formel zu löschen

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 in Excel Zellen zu überschreiben, ohne die zugrunde liegende Formel zu verlieren, kannst Du VBA nutzen. Hier ist eine Schritt-für-Schritt-Anleitung:

Öffne den VBA-Editor:
- Drücke ALT + F11, um den Visual Basic for Applications (VBA) Editor zu öffnen.
Wähle das richtige Arbeitsblatt:
- Doppelklicke im Projektexplorer auf das Arbeitsblatt, in dem Du die Änderungen vornehmen möchtest.

Füge den Code ein:

Nutze den folgenden VBA-Code, um sicherzustellen, dass Du in der Zelle G718:G797 schreiben kannst, ohne den Bezug zu verlieren:

Private Sub Worksheet_Change(ByVal Target As Range)
   Const adRelBer As String = "G718:G797"
   Const adQBer As String = "$B$19"
   Dim relBer As Range, Ziel As Range
   On Error GoTo ex
   Set relBer = Intersect(Target, Range(adRelBer))
   If Not relBer Is Nothing Then
       Application.EnableEvents = False
       For Each Ziel In relBer
           If Ziel = "" Then Ziel.Formula = "='" & Sheets("Nutzungsdauern").Cells(19, 2).Address & "'"
       Next Ziel
   End If
ex:
   Application.EnableEvents = True
End Sub

Speichere Deine Änderungen:
- Schließe den VBA-Editor und speichere die Excel-Datei als Makro-fähige Arbeitsmappe (.xlsm).
Testen:
- Trage Werte in die Zellen G718:G797 ein und überprüfe, ob der Bezug zu $B$19 erhalten bleibt.

Häufige Fehler und Lösungen

Fehler: Bezug wird nicht wiederhergestellt:
- Überprüfe, ob der VBA-Code richtig eingefügt wurde und keine Syntaxfehler enthält.
Fehler: Makro läuft nicht:
- Stelle sicher, dass die Makros in Deiner Excel-Version aktiviert sind. Gehe zu Datei > Optionen > Trust Center > Einstellungen für das Trust Center > Makroeinstellungen.

Alternative Methoden

Wenn Du kein VBA verwenden möchtest, kannst Du auch die Funktion „Daten einfügen“ verwenden, um Werte in Zellen einzufügen, ohne die Formeln zu überschreiben. Hier sind die Schritte:

Kopiere den gewünschten Wert.
Rechtsklick auf die Zielzelle und wähle „Inhalte einfügen“.
Wähle „Werte“ aus, um nur den Wert einzufügen und die bestehende Formel zu erhalten.

Praktische Beispiele

Beispiel 1: Du möchtest in Zelle G718 einen Wert eintragen, der den Bezug zu $B$19 nicht überschreibt. Verwende den oben genannten VBA-Code und trage einen neuen Wert ein.
Beispiel 2: Um eine Zeile in Excel zu löschen, ohne den Bezug zu verlieren, kannst Du die Zelle auswählen, die =Nutzungsdauern!$B$19 enthält, und sie über die Funktion „Inhalte einfügen“ ersetzen.

Tipps für Profis

Zellen verbinden ohne Inhalt zu verlieren: Wenn Du Zellen verbinden möchtest, ohne den Inhalt zu verlieren, nutze die Funktion „Zellen verbinden“ und wähle „Inhalte beibehalten“.
Datenüberprüfung: Setze eine Datenüberprüfung in den Zellen, um sicherzustellen, dass nur bestimmte Werte eingegeben werden, die die bestehende Formel nicht verletzen.

FAQ: Häufige Fragen

1. Wie kann ich in Excel in einer Zelle schreiben, ohne die Formel zu überschreiben? Um in Excel in einer Zelle zu schreiben, ohne die Formel zu überschreiben, kannst Du VBA verwenden, um die Eingabe zu steuern, oder die Funktion „Inhalte einfügen“ nutzen.

2. Was passiert, wenn ich eine Zelle lösche, die eine Formel enthält? Wenn Du eine Zelle, die eine Formel enthält, löschst, wird der Bezug zu der Formel verloren gehen. Um dies zu vermeiden, solltest Du die oben genannten Methoden verwenden, um den Bezug zu erhalten.

Beliebteste Forumthreads (12 Monate)

Zeilen überschreiben ohne Formel zu löschen

Forumthread: Zeilen überschreiben ohne Formel zu löschen

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Zeilen überschreiben ohne Formel zu löschen

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)

Zeilen überschreiben ohne Formel zu löschen

Forumthread: Zeilen überschreiben ohne Formel zu löschen

6 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Zeilen überschreiben ohne Formel zu löschen

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)

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread