Sie wollen in #Excel eine #Zahl mit #Text kombinieren und dabei #schön #formatieren – aber mit Zahlenformaten wollen Sie sich nicht herumschlagen? Der Standard – Tausenderpunkte, zwei Nachkommastellen – reicht Ihnen?
In diesem Fall empfehle ich Ihnen FEST(Zahl). Diese Funktion verwandelt Ihre Zahl in einen Text mit Tausenderpunkten (wenn nötig) und zwei Nachkommastellen – Juhu!
Wie kriegt man in #Excel Zahlen schön #formatiert (z.B. mit Tausenderpunkten) in eine Zelle – in Kombination mit anderen #Textinhalten?
Ein Beispiel:
In A1 steht der Vorname, in B1 der Nachname, in C1 irgendeine Zahl. Und wir brauchen (warum auch immer) die Textwurst Vorname Nachname: Zahl – wobei die Zahl „schön“ sein soll, also mit Tausenderpunkt und immer 3 Nachkommastellen, und damit es sich auszahlt auch noch eine Besonderheit bei negativen Werten.
Da hilft uns nun die Funktion TEXT(Wert;Format) aus der Patsche: sie formatiert einen Wert entsprechend dem angegebenen Format. Für unser Beispiel würde ich die Formel
=A1&“ “ &B1&“: „&TEXT(C1;“#.##0,000;- #.##0,000„)
verwenden (also einfach alles mit dem &-Operator zusammenhängen: Vorname, Leerzeichen, Nachname, Doppelpunkt-mit-Leerzeichen, schöne Zahl),
dabei ist #.##0,000;- #.##0,000 das zu verwendende Zahlenformat – hier geht fast alles, was auch beim benutzerdefinierten Zahlenformat möglich ist;
oder alternativ
=VERKETTEN(A1;“ „;B1;“: „;TEXT(C1;“#.##0,000;- #.##0,000„)).
Sie machen eine #Arbeitsmappe auf, und #Excel schreit sofort los, da sei ein #Zirkelbezug. Die Aufregung ist verständlich – aber wie finden Sie den #Übeltäter?
Er ist hier angeprangert: FORMELN > Formelüberwachung > Fehlerüberprüfung > Zirkelbezüge.
Und wo es zirkelt sehen Sie, wenn Sie einen der Zirkelbezüge anhaken, mit Spuren – ganz ganz deutlich ist hier zu sehen, dass der Vorgänger von A1 auch gleichzeitig der Nachfolger ist und vice versa – ein Zirkelbezug eben.
Sie müssen Ihre Formel korrigieren. Excel kann Ihnen da nicht weiterhelfen, da das Problem kein technisches sondern ein inhaltliches ist. Jemand hat sich beim Formelschreiben vertan und dann trotz Warnung einfach gespeichert.
Na so was!
Was ist denn eigentlich ein #Zirkelbezug?
Ganz einfach: Sie wollen eine Zelle berechnen und dabei Werte aus einer Zelle verwenden, die wiederum aufgrund der ersten Zelle berechnet werden. Die Berechnung soll sozusagen „im Kreis gehen“. Und das geht eigentlich nicht.
Beispiele? Ein einfaches wäre:
In Zelle A1 steht: =B1+2
In Zelle B1 steht: =A1*5
A1 soll also um 2 mehr sein als B1, welches das Fünffache von A1 ist, das um 2 mehr ist als B1, welches … Sie sehen schon, das lässt sich ewig so weiterdrehen, es ergibt keinen Sinn und wird daher von Excel nicht ausgewertet.
Auch häufig ist eine falsch eingestellte Summe:
steht etwa in E5 die Formel =SUMME(A5:E5), dann liegt ein Zirkelbezug vor, da in E5 die Summe aller Zahlen von A5 bis E5 errechnet werden soll – E5 wird aber gerade errechnet und kann sich nicht selbst mit hinzuaddieren.
Wenn Sie FORMELN > Berechnung > Berechnungsoptionen > Automatisch eingestellt haben (das ist Standard), dann werden Sie gewarnt:
Bei FORMELN > Berechnung > Berechnungsoptionen > Automatisch „gewinnt“ hingegen die zuletzt eingegebene Formel.
Sie brauchen für irgendeine #VBA #Aktion in #Excel nicht den benutzten Bereich (das wär ja der #UsedRange), sondern nur die Liste, in der sich Ihre Markierung befindet?
Bitte sehr, das können wir machen. Halten Sie sich fest 🙂
Range(Range("A1"), Cells(Range("A1").End(xlDown).Row, Range("A1").End(xlToRight).Column)).Select
markiert den Bereich ab A1 (Range(„A1“)) bis zu der Zelle (Cells), die
in der Zeile liegt, die von A1 aus nach unten erreicht wird, wenn Sie Strg+Pfeil-nach-unten drücken – also die Zelle vor der ersten leeren (Zeilenangabe in Cells)
und in der Spalte liegt, die Sie mit derselben Methode von A1 aus nach rechts erreichen (Spaltenangabe in Cells)
Kopieren Sie sich das gerne – es ist ein super-hilfreicher und von mir so oder ähnlich häufig verwendeter Schnipsel.
Gestern haben wir von einem #Bereich ausgehend das untere #Ende des befüllten #Bereichs mit #VBA adressiert. Geht das auch mit dem #oberen, #rechten, #linken Ende?
Ja.
Range("C5").End(xlUp) Range("C5").End(xlToLeft)
Range("C5").End(xlToRight)
gehen ans obere / linke / rechte Ende, ausgehend von C5. Analog zum Drücken von Strg+Pfeil-nach-oben/links/rechts.
Warum einmal mit und einmal ohne „To“? Keine Ahnung. Ich bin für die Konstantennamensgebung nicht verantwortlich. Irgendwann merkt man’s sich.
Wie können Sie das „untere Ende“ eines befüllten #Bereichs in #Excel mit #VBA ansprechen?
Range("A1").End(xlDown)
bezeichnet die Zelle, die unter (xlDown) der Zelle A1 die letzte befüllte ist. Also die Zelle, die vor einer leeren Zelle kommt.
Ist die Zelle unter der Startzelle leer, so erreichen Sie damit die nächste befüllte Zelle.
Ist die Startzelle die letzte befüllte Zelle, so erreichen Sie das Ende der Arbeitsmappe.
Hier geht es (im Gegensatz zum UsedRange) nur um mit Inhalt befüllte Zellen. Formatierte Zellen spielen nicht mit. (Wenn Sie in Excel arbeiten, erreichen Sie diese Zelle mit der Tastenkombination Strg+Pfeil-nach-unten.)
Was bedeutet #Me, wenn Sie in #Excel mit #VBA programmieren?
Also: mit Me qualifizieren Sie in einem Klassenmodul einer Tabelle, dass es bei dem folgenden Objekt um ein Objekt eben dieser Tabelle geht. Zum Beispiel wählen Sie mit
Private Sub Worksheet_Activate() Me.UsedRange.Select End Sub
den benutzten Bereich eben jener Tabelle aus, in deren Klassenmodul der Codeschnipsel steht – und zwar dann, wenn sie aktiviert wird. Egal, wie diese Tabelle heißt, egal, ob sie gerade aktiviert ist. Die Tabelle spricht sozusagen mit sich selbst – daher also Me.
Wie können Sie mit #VBA den Bereich ansprechen, der auf Ihrem #Excel #Arbeitsblatt „benutzt“ wurden?
Es gibt ein Objekt dafür: den UsedRange. Das ist ein rechteckiger Bereich, der die am weitesten links liegende, benutzte (also nicht im Urzustand befindliche) Zelle, die am weitesten rechts liegende, die am weitesten oben und die am weitesten unten liegende Zelle einschließt.
ActiveSheet.UsedRange.Select
markiert dann z.B. den gesamten benutzen Bereich des gerade aktiven Tabellenblatts. Das sieht dann zum Beispiel so aus:
„Benutzt“ bedeutet also: mit Inhalt befüllt ODER formatiert.
Ein kurzes aktuelles Intermezzo hat die Serie zum Theme #Excel #Bereich mit #VBA unterbrochen – aber nicht beendet. Weiter geht’s:
Den Bereich von C4 bis Z9 können Sie nicht nur wie letzte Woche beschrieben ansprechen, sondern auch so:
Range(Cells(4, 3), Cells(9, 26))
Gemeint ist der Bereich, der von der Zelle in der 4. Zeile und der 3. Spalte bis zur Zelle in der 9. Zeile und der 26. Spalte „aufgespannt“ wird.
Merken Sie den Unterschied? Sie brauchen keinen Spaltenbuchstaben zu kennen, sondern suchen sich einfach zwei Ecken aus, die Sie über die Cells-Auflistung referenzieren.
Anmerkung: wieder gilt alles, was bereits zum Thema Qualifizierung geschrieben wurde.
Anmerkung2: Es ginge natürlich auch so:
Range(Range("C4"), Range("Z9"))
katharinakanns
Soprani Software 