Office: Pluszeichen bei pos. Werten mit benutzerdef. Formaten in markiertem Bereich voranstellen

Hallo zusammen,

ich habe folgendes Problem: Ich möchte in hunderten Tabellen bzw. tausenden Zellen positiven Werten ein "+"-Zeichen voranstellen (also z.B. "+0,4" statt "0,4") und würde das klassicherweise über das benutzerdefinierte Format "+0,0;-0,0;0,0" lösen.

Erschwerend kommt nun aber hinzu, dass ich dieses vorangestellte Pluszeichen a) nicht bei allen positiven Werten angezeigt bekommen will - sondern nur in bestimmten, markierten Bereichen, die sich in zahlreichen Tabellenblättern befinden - und b) die betreffenden Zellen teilweise bereits ein benutzerdefiniertes Format haben (z.B. "0,0\*" oder "0,0\~").

Ich bin nun also auf der Suche nach einer möglichst automatisierten Lösung, die mir Folgendes umsetzt:

Nur in markierten Bereichen in mehreren Sheets:
WENN "0,0\*" DANN "+0,0\*"
UND
WENN "0,0\~" DANN "+0,0\~"
UND
WENN "0,0" DANN "+0,0"

Zur Veranschaulichung ist im Anhang noch eine Beispieltabelle zu finden.

Leider komme ich nach langem Probieren nicht weiter, und ein händisches Einfügen der Pluszeichen ist aufgrund der großen Datenmenge keine Option.

Kann mir hierbei bitte jemand behilflich sein? Ich bin wirklich für jeden hilfreichen Hinweis oder Lösungsvorschlag dankbar!! :)

Herzliche Grüße
Julian

 

Hallo Julian,

das wird wohl nur über VBA/Makro gehen. Kommt dies für dich in Frage?

Gruß Sigi

 

Moin Julian,

ich stimme Sigi zu und beschreibe mal, was man in VBA dazu basteln könnte.

Um einen numerischen Wert mit einem +-Zeichen in eine Zelle zuschreiben, muss man ihn leider in Text umwandeln. Da ist dann Vorsicht geboten, wenn mit dem Wert weiter gerechnet werden soll. Falls das kein Problem ist, kannst Du eine Schleife bauen, die durch Dein Datenblatt läuft und ein + voranstellt, falls folgende Bedingungen erfüllt sind.

• der Wert in der Zelle ist numerisch und positiv
• die Zelle befindet sich in einem definierten Bereich

Die beiden Zellblöcke, die Du in Deiner Datei dargestellt hast, bräuchtest Du dann nicht mehr. Es würde einfach der linke Block modifiziert.

Grüße

 

oder als einfache Formel (und widerspreche mir damit selbst )...

 

Hallo Julian,

das ist auch ohne VBA möglich, wenn es eine eindeutige Bedingung für den Geltungsbereich gibt. Dann nämlich in einer Kombination Deiner benutzerdefinierten Zahlenformat für die Zellen und zusätzlich einer bedingten Formatierung mit benutzerdefinierten Zahlenformat: +0,0\*

Für Deine Beispieldaten würde z.B. folgende bedingte Formatierungsformel für den Geltungsbereich B2:G18 ausreichend sein:
=(LINKS(VERWEIS(9;1/(LINKS($A$1:$A2;1)="V");$A$1:$A2);1)="V")*(RUNDEN(B2;3)>0)*ISTZAHL(B2)
und dem benutzerdefinierten Zahlenformat: +0,0\*

Diese Formel wird aber noch länger, wenn z.B der Block A13:G15 verschoben wird nach die Zeile 6 und vor Zeile7. Aber vielleicht gibt es ja auch in Deinem Original eine andere einfachere Bedingung. Das Prinzip könnte so aber klar geworden sein, wie Du Dein Problem evtl. auch ohne VBA lösen könntest.

 

Hallo in die Runde,

ganz lieben Dank schon mal, dass Ihr Euch mit der Problematik befasst habt! Ich habe die beiden konkreten Vorschläge von @d'r Bastler und @neopa C ausprobiert, bekomme aber nicht das gewünschte Resultat.

Vermutlich liegt das aber daran, dass ich das Ziel nicht klar genug formuliert habe. Außerdem sollte in meinen Beispieldaten der rechte Tabellenblock (also ab Spalte I) nur zur Veranschaulichung dienen, wie die Tabelle am Ende aussehen soll. Ausgangssituation ist der linke Block.

Mein Ziel ist es, dass am Ende alle positiven Werte ein Pluszeichen vorangestellt haben, aber eben nur in einem bestimmten Bereich (in meiner Beispieltabelle ist das der Bereich B14:G18). Essentiell ist außerdem, dass alle bestehenden Formate in diesem Bereich exakt so beibehalten werden, nur am Ende bei positiven Werten mit + versehen. Sprich: Benutzerdefiniertes Format bleibt benutzerdefiniertes Format, Zahlenformat bleibt Zahlenformat - oder anders ausgedrückt:

0,0\* wird zu +0,0\*
0,0\~ wird zu +0,0\~
0,0 wird zu +0,0
(sämtliche negative Werte, egal ob Zahl oder benutzerdef., bleiben unberührt)

...oder in Bildern: Dieser Ausgangsbereich...


...soll am Ende so aussehen:


Die Lösung von @d'r Bastler führt bei mir zu dem Ergebnis, dass zwar ein + richtig vorangestellt wird, die benutzerdefinierten Formate aber verloren gehen und am Ende alle Werte im Zahlenformat formatiert sind (oder mache ich hier was falsch?). Bei der Lösung von @neopa C passiert bei mir leider überhaupt nichts.

Ist das Problem jetzt besser dargestellt?

Danke Euch!!

 

Hallo Julian,

auf mein Angebot (#2) bist du jetzt gar nicht eingegangen.
Also wiederhole ich:
Zitat: " das wird wohl nur über VBA/Makro gehen. Kommt dies für dich in Frage? "

Gruß Sigi

 

Oh nein, das tut mir Leid, @Sigi.21!!!! Ja, theoretisch wäre auch ein Makro eine Option, allerdings bin ich auf dem Gebiet eine 0...

Hättest du dafür eine Makro-Lösung?

 

Ja hab ich.

Da wären etwas Kenntnis über Makros hilfreich. Aber vielleicht geht's auch so.
Makrostart über:
MENÜ - Entwicklertools - Makros - unten die Arbeitsmappe auswählen - oben das Makro wählen - Ausführen
(jetzt habe ich doch noch schnell einen Button eingefügt)

Gruß Sigi

 

@Sigi.21 - UNFASSBAR, du bist ja der Hammer!! DANKE!

Drei Fragen noch:

1. Das VBA macht was es soll für die Ausgangsformate "0,0\*" und "0,0", nicht aber für "0,0\˜" (hochgestellte Tilde). Hier gibt es die Meldung "Keine zutreffenden Zellen gefunden". Woran könnte das liegen?
2. Wenn ich das VBA nun auf eine Vielzahl an Sheets gleichzeitig anwenden will, wie gehe ich dafür vor? Ideal für meine Umsetzung wäre, wenn ich zu Beginn händisch alle relevanten Bereiche im gesamten File markiere (also in mehreren Sheets), und deine "FormatAendern"-Funktion dann auf alle Markierungen gleichzeitig anwenden kann. Geht das? Oder kopiert man sich den Button von Sheet zu Sheet und führt die Funktion dann pro Sheet durch?
3. Kann man das VBA (und den Button) am Ende wieder entfernen, ohne dass die geänderten Formate verloren gehen, und der Nutzer nichts von dem Makro mitbekommt? Oder muss das File-Format .xlsm bleiben? Hätte am Ende gerne wieder .xlsx...

In jedem Fall schon mal tausend Dank - das hilft mir wirklich sehr weiter!!

 

Hallo Julian,

1. Format mit Tilde geht bei mir ohne den Backslash(\)! Da läuft auch das Makro.
2. Mehrere Sheets auf einmal geht def. nicht! Du musst Sheet für Sheet vorgehen, da das Makro mit der aktiven Markierung arbeitet. Dafür braucht vorher (od. im Makro) nichts festgelegt/definiert werden.
3. Du änderst doch die Formate in deiner (vba-freien) Mappe!!
Das Makro bleibt in der Originalmappe. Sie muss für das Laufen des Makros lediglich geöffnet sein. (auch im Hintergrund). In deiner Mappe werden lediglich die Formate geändert. Deshalb der START über MENÜ - Entwicklertools - Makros - unten die Arbeitsmappe auswählen - oben das Makro wählen - Ausführen

Gruß Sigi

 

Moin Julian,
die Lösung von Sigi hat natürlich schon beinahe luxuriöse Züge, mit Userform und variabler Umwandlung und so. Gute Arbeit!
Vielleicht tut es aber auch eine einfachere Variante. Die Kommentare erklären, was die jeweilige Zeile macht.

Code:

Option Explicit

Sub PlusPlus()
Dim i As Integer, j As Integer, c As Integer, r As Integer, b As Integer

c = UsedRange.Columns.Count 'zählt die belegten Spalten der Tabelle
r = UsedRange.Rows.Count    'zählt die belegten Zeilen der Tabelle

For i = 1 To r              'eine Schleife durch alle belegten Zellen der Spalte A
    If Left(Cells(i, 1), 3) = "Ver" Then    'um den Anfang des Wortes Veränderung zu finden
         b = i                              'und als Startpunkt für die nächste Schleife zu setzen
         GoTo DoIt                          'gefunden? also mach!
    End If
Next i          'Schleifenreturn

DoIt:
For i = b To r              'eine Schleife durch alle belegten Zeilen ab de Fund "Ver"
    For j = 1 To c              'eine zweite Schleife durch alle Spalten
        If IsNumeric(Cells(i, j)) And Cells(i, j).Value > 0 Then    'prüft den Zellinhalt ob numerisch und positiv
            Cells(i, j).NumberFormat = "@"                          'wandelt das Zellformat in Text um
            Cells(i, j) = "+" & Cells(i, j)                         'und ergänzt das Plus-Zeichen
        End If
    Next j          'Schleifenreturn
Next i          'Schleifenreturn

End Sub

Um das auszuprobieren, klickst Du (rechte Maustaste) auf den Tabellenreiter unten links im Bildschirm, wählst Code anzeigen aus, fügst den Code per Copy 'n Paste in das große Fenster ein und schließt es über das X oben rechts.
Wieder in der Tabelle findest Du mithilfe von Alt+F8 das Makro PlusPlus zur Auswahl und führst es aus.

Nachtrag: Falls du das Makro für mehrere Blätter brauchst, lässt sich das leicht anpassen oder auch (ganz simpel) in die jeweilige Tabelle kopieren.

Viel Spaß! und Grüße

 

Moin nochmal,

einfach mal nachgereicht: Der neue Code gehört jetzt nicht mehr in das einzelne Tabellenblatt (dort also bitte löschen!), sondern in ein Allg. Modul. Dazu öffnest Du per Alt+F11 den VBA-Editor >> Rechtsklick in das kleine Fenster links oben VBA - VBA Projekt >> Einfügen >> Modul und kopierst den geänderten Code dort hinein. Jetzt steht das Makro in jedem Tabellenblatt über Alt+F8 zur Verfügung.

Code:

Sub PlusPlus()
Dim i As Integer, j As Integer, c As Integer, r As Integer, b As Integer

With ActiveSheet        'Das Makro bearbeitet nur die Zellen des aktiven Arbeitsblatts
c = .UsedRange.Columns.Count 'zählt die belegten Spalten der Tabelle
r = .UsedRange.Rows.Count    'zählt die belegten Zeilen der Tabelle

For i = 1 To r              'eine Schleife durch alle belegten Zellen der Spalte A
    If Left(.Cells(i, 1), 3) = "Ver" Then    'um den Anfang des Wortes Veränderung zu finden
         b = i                              'und als Startpunkt für die nächste Schleife zu setzen
         GoTo DoIt                          'gefunden? also mach!
    End If
Next i          'Schleifenreturn

DoIt:
For i = b To r              'eine Schleife durch alle belegten Zeilen ab dem Fund "Ver"
    For j = 1 To c              'eine zweite Schleife durch alle Spalten
        If IsNumeric(.Cells(i, j)) And .Cells(i, j).Value > 0 Then    'prüft den Zellinhalt ob numerisch und positiv
            .Cells(i, j).NumberFormat = "@"                          'wandelt das Zellformat in Text um
            .Cells(i, j) = "+" & Cells(i, j)                         'und ergänzt das Plus-Zeichen
        End If
    Next j          'Schleifenreturn
Next i          'Schleifenreturn

End With     'schließt den Bearbeitungsbereich

End Sub

Viel Spaß!

 

Anmerkung für andere VBAstler:

Nachdem es sich in größeren Projekten eher nicht so schickt, mit Sprunganweisungen zu arbeiten, hier noch die Rekursiv-Variante der Schleife ohne Goto:

Code:

For i = r to 1 Step - 1
     If Left(Cells(i,1),3)  =  "Ver" Then
          b = i
     End If
Next i

 

Vielen Dank euch allen für die großartige Unterstützung! Das hätte ich beim besten Willen nicht erwartet - DANKE!