EXCEL 2007 Automatisch Minus in Zelle schreiben

Computer: Software & Programmierung Office Software
1

Hallo Zusammen,
ich benötige einen VBA-Code der in unterschiedlichen Spalten einer Tabelle (als Tabelle formatiert) ein Minus Strich zurück schreibt, wenn der Inhalt gelöscht wird bzw. wenn nichts in der Zelle steht. Die Grundformatierung sollte also immer ein Minus Zeichen sein. Dieses Minus Zeichen benötige ich um andere Formel ausführen zu können. Im Netz habe ich folgenden VBA Code gefunden der schon nahe an meine Lösung kommt, jedoch müssten noch einige Parameter mit eingebaut werden. Hier der Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Bereich As Range
Dim Zelle As Range
Set Bereich = Range(„A1:B10“) 'ANPASSEN!
For Each Zelle In Bereich
If Zelle.Value = „“ Then
Zelle.Value = „—“
End If
Next
End Sub

Folgende Parameter müssen intergiert werden:
Spaltenbereich H4:H? (? Soll sich automatisch den Zeilen der Tabelle anpassen)
Spaltenbereich AO4:AO? (? Soll sich automatisch den Zeilen der Tabelle anpassen)
Spaltenbereich AR4:AR? (? Soll sich automatisch den Zeilen der Tabelle anpassen)
Spaltenbereich AZ4:AZ? (? Soll sich automatisch den Zeilen der Tabelle anpassen)
Usw. weitere sollte ich einfach dann hinzufügen können durch kopieren im VBA um nicht alle Spaltenbereiche hier jetzt aufführen zu müssen.

Bei dem oben angegebenen Code funktioniert es leider nicht mit Kopieren und den Bereich zu ändern. Dann nimmt er immer nur den letzten Bereich und führt den Befehl dort aus. Eine Anpassung an die Zeilen der Tabelle erfolgt auch nicht, sondern nur im angegebenen Bereich. Erweitere ich den Bereich, führt er den Befehl in diesem komplett aus und legt mir zusätzliche Zeilen an, was unerwünscht ist. Der Bereich soll sich der Anzahl ein Zeilen der Tabelle anpassen können.

Hat Jemand eine Lösung für mich, wie ich den obigen Code modifizieren kann auf mein Problem ?

Vielen Dank

Gruss Rossi

2

Dieses Minus Zeichen benötige ich um andere Formel ausführen zu können

Hallo Rossi,
dieser Satz nötigt mich dazu mal die Frage zu stellen, ob das wirklich eine sinnvolle Vorgehensweise ist.

Du löscht den Inhalt einer Zelle um ihn dann wieder mit einem Minus zu beschreiben. Hier erkenne ich den Sinn nicht!

Das Makro müsste ja, prüfen ob die Zelle tatsächlich leer ist.
Das kann deine andere Formel genau so, auch ohne Minis in der Zelle.
zB. die Funktion =istleer(a1)könnte das übernehmen.

Eine leere Zelle lässt sich per bedingter Formatierung einfärben um sie kenntlich zu machen.

Gruß Holger

3

Hi,

vor allem könnte man das auch mit einer benutzerdefinierten Formatierung erreichen:

#.##0,00;[Rot]- #.##0,00;"—";@

Aber der Anfrager schreibt ja, er braucht das Minus den Strich, um in anderen Zellen irgendwas zu berechnen.

M.

4

Aber der Anfrager schreibt ja, er braucht den Strich, um in anderen Zellen irgendwas zu berechnen.

Ja, genau, daher meine Frage ob man die Formel nicht so anpassen kann das sie Leere Zellen erkennt. Dann benötigt man gar kein Makro

Gruß Holger

5

Hallo Rossi,

du solltest die Vorschläge von Holger und Markus ernsthaft in Erwägung ziehen.

Ansonsten müsste das Makro etwa wie eines der beiden folgenden aussehen.
Die Laufzeit der Makros ist aber relativ lang.

Gruß
Franz

Private Sub Worksheet\_Change(ByVal Target As Range)
 'nur die jeweils geänderten Zellen werden neu berechnet
 Dim objList As ListObject, StatusCalc As Long
 Dim Zelle As Range
 Set objList = Me.ListObjects(1)
 If Not Intersect(Target, Me.ListObjects(1).DataBodyRange) Is Nothing Then
 With Application
 .EnableEvents = False
 .ScreenUpdating = False
 StatusCalc = .Calculation
 .Calculation = xlCalculationManual
 End With
 For Each Zelle In Target
 Select Case Zelle.Column
 Case 8, 41, 44, 52 'Nummern der Spalten mit "—"
 If Zelle.Value = "" Then
 Zelle.Value = "—"
 End If
 Case Else
 'do nothing
 End Select
 Next
 With Application
 .EnableEvents = True
 .ScreenUpdating = True
 .Calculation = StatusCalc
 End With
 End If
End Sub

'oder

Private Sub Worksheet\_Change(ByVal Target As Range)
 'Alle Zellen in den Spalten werden geprüft
 Dim Bereich As Range, Zelle As Range, Spalte As Long, StatusCalc As Long
 With Application
 .EnableEvents = False
 .ScreenUpdating = False
 StatusCalc = .Calculation
 .Calculation = xlCalculationManual
 End With
 With Me.ListObjects(1).DataBodyRange
 For Spalte = .Column To .Column + .Columns.Count - 1
 Select Case Spalte
 Case 8, 41, 44, 52 'Nummern der Spalten mit "—"
 Set Bereich = Me.Range(Me.Cells(.Row, Spalte), Me.Cells(.Row + .Rows.Count - 1, Spalte))
 For Each Zelle In Bereich.Cells
 If Zelle.Value = "" Then
 Zelle.Value = "—"
 End If
 Next
 Case Else
 'do nothing
 End Select
 Next
 End With
 With Application
 .EnableEvents = True
 .ScreenUpdating = True
 .Calculation = StatusCalc
 End With
End Sub
6

Hallo Rossi,

einer Tabelle (als Tabelle formatiert)

was meinst du damit genau? Du hast auf einem Tabellenblatt
einen festen Zellbereich den du ggfs. mit Rahmen usw. als eine
Tabelle gekennzeichnet hast?
Oder etwas anderes?

schreibt, wenn der Inhalt gelöscht wird bzw. wenn nichts in
der Zelle steht.

Okay, wie schon Franz indirekt durch seine zwei Codeversionen
demonstriert ist es als Fremder unklar was du da genau willst.

Angenommen, in H4:H8 stehen Werte.
Jetzt schreibst du in H12 einen Wert.

Angenommen, in H4:H8 und H12 stehen Werte.
Jetzt löschst du den Inhalt von H12.

Was soll in den Fällen der Code tun?
Soll in beiden Fällen dann H9:H11 mit deinem speziellen Minus bedacht werden, im letzteren Fall zusätzlich auch H12?

Die Grundformatierung sollte also immer ein
Minus Zeichen sein. Dieses Minus Zeichen benötige ich um
andere Formel ausführen zu können.

Ich schließe mich Holger und Markus an. Verrate mal mehr über deinen
Plan, zumindest hat es den großen Anschein als könnte man sich diesen „Minus-Einfügen“-Schritt sparen.
Hinweis, mit dem obersten Link i9n FAQ:2606 kannst du uns eine Mappe
hochladen, bezogen auf diese Mappe kannste dann ggfs. besser erklären
was du da planst. Und wir können genauer nachfragen…

Gruß
Reinhard

7

Hallo Zusammen,
erstmal vielen Dank für Eure Hilfe und Anmerkungen. Habe gemerkt das meine Ausführung wohl nicht genau genug sind um das Problem zu beschreiben. Einen Auszug meiner Datei habe ich hier hochgeladen.

http://www.file-upload.net/download-4529255/2012-07-…

  1. Die Tabelle ist komplett als Tabelle formatiert, um automatisch die Zeilenerweiterung der Tabelle mit den festgelegten Formatierung und Formeln zu ermöglich.

  2. Mit dem Minusstrich werden verschieden Parameter zur Berechnung herangezogen. Bsp. Wenn in Zelle XY = -, dann Wert aus Zelle XZ, sonst Wert aus Zelle XY für Berechnung nehmen. Um nun nicht ständig einen Minusstrich in die Zelle (grüne Spalten) dann eingeben zu müssen würde ich das gerne automatisieren, das wenn ich den Wert aus Zelle XY lösche, dort ein Minusstrich erscheint, der von Excel auch so erkannt wird. Über benutzerdefinierte Formatierung erkennt Excel den eingegeben – nicht als Minus und gibt eine Fehlermeldung aus.

  3. Diese Formatierung benötige ich für verschieden Spalten in meiner Tabelle, da der Minusstrich immer die Auswahl deklariert, welcher Wert für die Berechnung herangezogen wird. Diese Formatierung sollte sich auch mit dem einfügen einer neuen Zeile in meiner Tabelle automatisch erweitern.

  4. Die Formatierung bezieht sich immer nur auf eine Zeile und nicht übergreifend. Damit meine ich, das eine Eingabe oder löschen einer Zelle nur Auswirkungen auf diese Zeile hat. Bsp. Ein löschen des Wertes in Zelle XY soll einen Minusstrich ergeben der den Parameter in einer anderen Spalte dieser Zeile aktiviert und zur Berechnung heran zieht.

Für die grünen Bereiche in der Tabelle benötige ich diese Formatierung. Ich hoffe das Problem ist klarer geworden.

Gruss Rossi

8

http://www.file-upload.net/download-4529255/2012-07-…

Hallo Rossi,

  1. Die Tabelle ist komplett als Tabelle formatiert, um
    automatisch die Zeilenerweiterung der Tabelle mit den
    festgelegten Formatierung und Formeln zu ermöglich.

ich sehe daß du da A2:CR3 dem Namen „Tabelle1“ gegeben hast.
Formeln sehe ich keine.

Für die grünen Bereiche in der Tabelle benötige ich diese
Formatierung. Ich hoffe das Problem ist klarer geworden.

Zellen von 12 Spalten sind in Zeile 2 ^ 3 eingefärbt. Dort steht ein Minuszeichen drin.
Nachfrage, es geht nur um diese Zellen/Spalten?
Und in diesen speziellen Zellen wird von dir manuell immer nur eine
abgeändert, nicht 10 auf einmal?

Hast du die Lösung von Franz ausprobiert?
Was ich noch immer vermisse ist die Ansicht der Formeln die dann da dieses Minuszeichen verwerten.

Was bedeuten da diese ca. 20 vergebenen Namen die quasi nur „Bezug#“
als Formel haben?

Gruß
Reinhard

9

Hallo Rossi,

  1. Die Tabelle ist komplett als Tabelle formatiert, um
    automatisch die Zeilenerweiterung der Tabelle mit den
    festgelegten Formatierung und Formeln zu ermöglich.

ich sehe daß du da A2:CR3 dem Namen „Tabelle1“ gegeben hast.
Formeln sehe ich keine.

Das ist meine Tabelle. Die Formel habe ich rausgenommen, da Sie auf andere Tabellenblätter zugreift, die ich leider nicht rausgeben kann.

Für die grünen Bereiche in der Tabelle benötige ich diese
Formatierung. Ich hoffe das Problem ist klarer geworden.

Zellen von 12 Spalten sind in Zeile 2 ^ 3 eingefärbt. Dort steht ein Minuszeichen drin.
Nachfrage, es geht nur um diese Zellen/Spalten?
Und in diesen speziellen Zellen wird von dir manuell immer nur eine
abgeändert, nicht 10 auf einmal?

Es betrifft nur die 12 Spalten mit der Einfärbung, wobei die Zellen sich erweitern wenn ich eine neue Zeile anlege. In diese Zellen wird eine Zahl manuell reingeschrieben von mir, mit der eine Formel dann rechnet. Die 12 Spalten sind für verschiednen Formeln und nicht für eine einzige. Wenn ich die Zahl wieder lösche, muss ein Strich erscheinen, wodurch eine andere Zahl zur Berechnung aus einer anderen Spalte verwendet wird, die aus anderen Tabellenblätter mit Sverweis entnommen wird. Selbstverständlich könnte ich alle 12 Spalten in einer Zeile beliebig ändern. Dies hat aber keine Auswirkung auf die gesamte Zeile, sondern nur auf die Formel, mit der die Verknüpfung besteht. Kurz gesagt, brauche ich ein Makro, was beim Löschen des Inhalts einen Minusstrich produziert, wodurch die Formel dann weiss, das für die Berechnung ein anderer Wert aus einer anderen Spalte verwendet wird.

Hast du die Lösung von Franz ausprobiert?
Was ich noch immer vermisse ist die Ansicht der Formeln die dann da dieses Minuszeichen verwerten.

Ja, habe ich, hat leider nicht funktioniert.

Was bedeuten da diese ca. 20 vergebenen Namen die quasi nur „Bezug#“
als Formel haben?

Durch löschen der anderen Tabellenblätter ist der Bezug weg.

Die Formel sieht beispielsweise so aus:

WENN(D3="-";C3*E3;D3*E3)
für hier im Beispiel benötige ich bei D3 dieses Makro.

Gruss Marco

10

Hallo Marco,

Das ist meine Tabelle. Die Formel habe ich rausgenommen, da
Sie auf andere Tabellenblätter zugreift, die ich leider nicht
rausgeben kann.

sehr schade, grad an sowas bin ich immer interessiert :smile:

Selbstverständlich könnte ich alle 12 Spalten in einer
Zeile beliebig ändern.

Okay, ich habe nicht klar genug formuliert was ich von dir wissen
wollte. Mir ging es einzig darum zu wissen, ob du jetzt z.B. in
H2 etwas manuell abänderst oder aber ob es auch vorkommen kann
daß du durch Reinkopieren von Werten von irgendwo her im
gleichen Vorgang mehrere Zellen abänderst.

Noch sehe ich keinen Bedarf für ein makro, dazu weiter unten mehr.
Jedenfalls je nach Anzahl, eine oder mehr gleichzeitig
geänderten Zelle(n) sieht dan evtl. Code leicht anders aus.
Beides ist aber problemlos lösbar.

Eine neue Frage, diese grünen Spalten da, sind die „fest“ oder
kann da mal eine Spalte dazukommen oder wegfallen?

„fest“ bedeutet ich schreibe in den Code fest die Spalten H, AS usw.
Bei Änderungen müßtest du dann im Code die vorgegeben Spalten abändern, nur dann wenn sich da was geändert hat.

Oder aber ich prüfe im Code der auf Zelländerungen reagiert immer ab
ob die Änderung in einer grünen Zelle stattfand.

Hast du die Lösung von Franz ausprobiert?
Was ich noch immer vermisse ist die Ansicht der Formeln die
dann da dieses Minuszeichen verwerten.

Ja, habe ich, hat leider nicht funktioniert.

Bittebitte, jetzt nicht mehr nötig da ich das gleich selbst teste,
sag immer mehr zu so Fragen als „geht nicht“, „funktioniert nicht“.
Was genau geht nicht, ist der PC abgequalmt *gg*, tat sich rein gar
nix, gab es Fehlermeldungen, wenn ja in welcher Codezeile, was
genau passierte für dich wahrnehmbar als du den Code von Franz,
welchen, testeste?

WENN(D3="-";C3*E3;D3*E3)
für hier im Beispiel benötige ich bei D3 dieses Makro.

Das ist doch genau das was nicht nur ich noch nicht kapiert haben :frowning:
Warum passt dir:
WENN(D3="";C3*E3;D3*E3)
nicht? Dann fällt doch der Aufwand mit dem Minuszeichen weg.

Achja, ich hoffe du glaubst daran was man im Theater so sagt,
wenn die Proben völlig mißlingen umso besser wird dann die
erste Aufführung :smile:
Denn außer meinen nervenden Nachfragen gibt es einen neuen
Bremsklotz, deine Mappe ist nicht mehr runterladbar, zumindest
für mich, FF 12.0 auf Win XP.

Gruß
Reinhard

11

Hallo,

ich habe jetzt die Tabelle mit einem kompletten Block (Block 1) zum download zur Verfügung gestellt. Dort kannst Du nun die Abhängigkeiten erkennen. Von diesen Blöcken habe ich mehrere die in sich rechnerisch zusammen hängen und autark von den anderen Berechnungen sind.

Da es verschiedene Zusammenhänge gibt, wurden diese mit dem Minusstrich als Möglichkeit und in Kombination verbunden und als Parameter in der Berechnung bzw. Auswahl bestimmt. Daher kann ich es nicht mit „“ zelle auswählen.

Es kann natürlich sein, das ich Spalten hinzufügen muss, dann muss ich den Code mit dieser Spalte ergänzen, bzw. andere neu zu weisen.

Zu der Variante von Franz: Es geht nicht ist bezogen auf das Ergebnis -> es wurde kein Strich in den Zellen erzeugt.

Schau Dir die Tabelle doch mal an, nun sollte Sie Deine Fragen beantworten können.

Gruss Rossi

http://www.file-upload.net/download-4531357/2012-07-…

12

http://www.file-upload.net/download-4531357/2012-07-09-
Berechnung.xlsm.html

Hallo Marco,

Da es verschiedene Zusammenhänge gibt, wurden diese mit dem
Minusstrich als Möglichkeit und in Kombination verbunden und
als Parameter in der Berechnung bzw. Auswahl bestimmt. Daher
kann ich es nicht mit „“ zelle auswählen.

okay, ich gebs auf :smile:

Es kann natürlich sein, das ich Spalten hinzufügen muss, dann
muss ich den Code mit dieser Spalte ergänzen, bzw. andere neu
zu weisen.

Mache das im nachstehen Code in dieser Codezeile:
Spalten = Array(„AP“, „AS“)
Code gehört in das Modul von „Berechnung“

Gruß
Reinhard

Option Explicit

Private Sub Worksheet\_Change(ByVal Target As Range)
Dim Von As Long, Bis As Long, Spalten, S As Long, Vorh As Boolean

'zu überwachende Spalten
Spalten = Array("AP", "AS")

'Pruefung ob mehr als eine Zelle
If Target.Count \> 1 Then Exit Sub

'Pruefung ob im Zellbereich "Tabelle1"
If Intersect(Target, Range("Tabelle1")) Is Nothing Then Exit Sub

'Pruefung ob die richige Spalte
For S = LBound(Spalten) To UBound(Spalten)
 If Target.Column = Range(Spalten(S) & "1").Column Then
 Vorh = True
 Exit For
 End If
Next S
If Vorh = False Then Exit Sub

Application.EnableEvents = False 'Change-Ereignis-Reaktion ausschalten
If Target.Value = "" Then Target.Value = "-" 'Zellwertkorektur
Application.EnableEvents = True 'Change-Ereignis-Reaktion einschalten
End Sub
13

Oh je, konnte ich das Problem nicht deutlich machen ? Dachte anhand der Formel kann man es erkennen.

Habe das Makro eingefügt und es funktioniert, Ebenso auch die Erweiterung auf andere Spalten. Vielen Dank.

Wenn ich eine neue Zeile ausfülle und sich die Tabelle automatisch erweitert, übernimmt er mir die Formatierung nicht der Spalten AL:AO. Woran kann das denn liegen ? Weiter wäre es super wenn in den überwachenden Spalten dann auch direkt ein Minusszeichen steht und ich nicht zuerst in diese Zelle klicken und löschen muss, damit der Minusstrich erzeugt wird. Kann man das in die VBA einbinden ? Füge doch mal in Zelle A7 ein Buchstaben ein, dann siehst du was ich meine.

Hier die Datei mit der VBA

http://www.file-upload.net/download-4533933/2012-07-…

Gruss Rossi

14

Hallo Marco,

Oh je, konnte ich das Problem nicht deutlich machen ? Dachte
anhand der Formel kann man es erkennen.

sorry, hatte mich nur auf Vba konzentriert, nicht auf die Formeln.

Habe das Makro eingefügt und es funktioniert, Ebenso auch die
Erweiterung auf andere Spalten.

Sehr schön.

Wenn ich eine neue Zeile ausfülle und sich die Tabelle
automatisch erweitert, übernimmt er mir die Formatierung nicht
der Spalten AL:AO. Woran kann das denn liegen ?

M.E. geschieht das auch in anderen Zellen.
Irgendwas mir unbekanntes geschieht dort. Ich vermute sehr es
liegt daran daß da paar Zellen anders formatiert sind als andere die
gleich aussehen.
Ggfs. am Namen Tabelle1 für einen Zellbereich.

Ist sowieso ein seltsamer Name. Kannst du ihn löschen? Ich nicht,
nicht mal klassisch mit Vba
Thisworkbooks.Name(„Tabelle1“).Delete

Abgesehen davon benutze ich schon Namen für Zellen/zellbereiche
aber mit den Einfügeregulariern wenn ich unterhalb dieses
Zellbereichs in eine Zelle einen Wert schreibe und dann diese
Zeile automatisch mit in den Zellbereichsnamen integriert wird
kenne ich mich Null aus.

Die evtl. Formatierungsunterschiede in A4:AV6 habe ich schon
liquitiert, d.h. ich formatiere mit Vba A4:AV6 komplett neu.
Leider geschieht das mit AL u.a. immer noch bei Einfügen in A7.

Weiter wäre es
super wenn in den überwachenden Spalten dann auch direkt ein
Minusszeichen steht und ich nicht zuerst in diese Zelle
klicken und löschen muss, damit der Minusstrich erzeugt wird.
Kann man das in die VBA einbinden ?

Ja.

Füge doch mal in Zelle A7
ein Buchstaben ein, dann siehst du was ich meine.

Habe ich ja gemacht deshalb wie oben geschrieben bin ich am Grübeln
nach welcher Logik Excel da vorgeht.

http://www.file-upload.net/download-4533933/2012-07-…

Gruß
Reinhard

15

Hallo Reinhard,

das Problem mit der Formatierung konnte ich lösen. Excel übernimmt die Grundformatierung der gesamten Spalte beim automatisch erweitern der Tabelle bei einem Eintrag.

Hast Du noch einen Tip für mich wie ich die VBA ändern muss, damit er automatisch ein Minusstrich beim erweitern der Tabelle einträgt ? Momentan lässt er die Zellen leer und ich muss diese anwählen, dann auf löschen drücken um dann das Minuszeichen zu erhalten.

Den Tabellennamen kann ich über Formeln, Namensmanager ändern. Löschen kann ich die Tabelle, in dem ich Sie markiere und über das Kontextmenü, Tabelle in Bereich konvertiere wieder umwandle.

Gruss Rossi