Excel, Zellenformatierung ändern

Question

Hallo,

durch ein Makro wird eine Zelle (Rechnungsnummer) als 26-000 formatiert, was ja auch so gewollt ist.

Im Makro steht u.a.: (nächsteLeereZelle.NumberFormat = "26-000")

Diese Zelle beinhaltet jetzt (entsprechend dem Makro) eine nach oben laufende Rechnungsnummer.

Diese Zelle ist jetzt benutzerdefiniert formatiert (26-000)

Der Wert der Zelle bei der Rechnungsnummer 26-001 zeigt mir allerdings nur 1 an, bei 26-002 nur 2, usw.

Für eine SVERWEIS Funktion benötige ich die komplette Nummer 26-001, jedoch funktioniert das leider nicht unter der benutzerdefinierten Formatierung, da nur der Wert 1 bzw. 2 usw. übernommen wird.

Kann man das Makro dahingehend ändern, dass es die Rechnungsnummern 26-000 als Standard Formatierung speichert, oder kann man die gespeicherten Zellen mit dem benutzerdefinierten Format irgendwie umwandeln in Standard Format, aber unter Beibehaltung des Rechnugnummernformats?

Hört sich sehr verwirrend an, ist es auch für mich.

Aber vielleicht hat jemand einen Vorschlag, wie ich die Problematik in den Griff kriegen könnte.

Mit freundlichen Grüßen

Answer 1

Hi,

das ist logisch, dass in der Zelle nur 1, 2, 3, usw. steht, da es sich schließlich um eine Anzeigeform handelt und nicht um tatsächliche Zellinhalte im Wert von 26-001 oder 26-002. Du kannst aber einfach eine Hilfsspalte verwenden, in der du die Zellinhalte in deine Werte 26-001 usw. umwandelst und deinen SVERWEIS() auf diese Spalte beziehen:

Angenommen deine auf "26-000" formatierten Zellen mit den Zellinhalten 1, 2, 3 stehen in Spalte A, dann kannst du mit der Formel ="26-"&TEXT(A1;"000") diese Zahlen umwandeln.

Eventuell musst du anstelle von SVERWEIS() die Funktion INDEX() verwenden, falls sich die Hilsspalte mit den umgewandelten Daten rechts von der Spalte mit deiner Bezugsspalte befindet.

Bis später, Karin

Answer 2

Hallo Karin!

Vielen lieben Dank für Deine Antwort und deinen Tipps, funktioniert prima!

Könntest Du bitte trotzdem, wenn Du die Zeit hast, Dir folgenden VBA Code ansehen, ob es möglich ist, die Zelle G4 und die Spalte A:A direkt in dem Code so zu formatieren, dass die Rechnungsnummern in der Form 26-000 als Text gespeichert werden.(Nur für mich zum Lernen)

Wenn nicht, ist OK!

Sub Rechnungsnummer_Generieren()

    Dim NächsteNummer As Long

    Dim LetzteNummer As Long

    Dim ZelleNummer As Range

    ' --- Anpassbare Bereiche ---

    Set ZelleNummer = Worksheets("Rechnungsnummern").Range("G4") ' Zelle, wo die aktuelle Nummer steht

    Dim ListeSpalte As Range

    Set ListeSpalte = Worksheets("Rechnungsnummern").Range("A:A") ' Spalte mit allen Nummern (optional, für MAX)

    ' --------------------------

    ' Prüfen, ob eine Nummer existiert (falls nicht, starte bei 1)

    If Application.WorksheetFunction.CountA(ListeSpalte) > 1 Then ' >1, da Überschrift in A1 sein könnte

        LetzteNummer = Application.WorksheetFunction.Max(ListeSpalte)

    Else

        LetzteNummer = 0 ' Startet bei 0, damit +1 = 1 ergibt

    End If

    NächsteNummer = LetzteNummer + 1

    ' Nummer formatieren (z.B. 00000)

    ZelleNummer.Value = Format(NächsteNummer, "26-000") ' Format anpassen, z.B. "000000"

    ' Optional: Die neue Nummer in die Liste schreiben (z.B. Spalte A)

    ' Suche die nächste leere Zelle in Spalte A

    Dim nächsteLeereZelle As Range

    Set nächsteLeereZelle = Worksheets("Rechnungsnummern").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

    nächsteLeereZelle.Value = NächsteNummer

    nächsteLeereZelle.NumberFormat = "26-000"

    ' Optional: Datum hinzufügen (z.B. 2025-00001)

    ' Worksheets("Rechnungen").Range("D1").Value = "Rechnungs-Nr.: " & Format(Date, "YYYY-") & Format(NächsteNummer, "00000")

End Sub

Nochmal vielen Dank für Deine Hilfe!

Beste Grüße

Hans-Jörg

Answer 3

Hi Hans-Jörg,

beschreibe bitte mal in Worten was du erreichen möchtest - ich habe dich so verstanden:

1. du möchtest in G4 eine Rechnungs-Nummer in der Form 1, 2, 5 usw. eintragen und diese soll ins Format "YY-0000", also z.B. 26-0005 umgewandelt werden

2. danach soll in Spalte A nach dieser Rechnungsnummer gesucht werden (falls vorhandenn, dann nichts machen). Falls nicht vorhanden, dann die letzte Rechnungsnummer nehmen und +1 und in die 1. leere Zelle in Spalte A eintragen sowie G4 umwandeln

3. in D1 soll "Rechnungs-Nr.:" & ..... eingetragen werden

Es stellt sich mir folgende Frage: soll mit einem neuen Jahr die Zählung in Spalte A wieder bei 1 beginnen oder sollen die laufenden Nummern unabhängig vom jJhr weitergeführt werden? Oder stehen in Spalte A nur Nummern aus ein und demselben Jahr, also wird mit dem Beginn eines neuen Jahres Spalte A wieder geleert?

Bis später, Karin

Answer 4

Hallo Karin!

Wenn ich auf meinen Button ( im Arbeitsblatt"Rechnungsnummern") drücke, der mit oben angegebenen VBA Code verknüpft ist, wird in Zelle G4 eine Rechnungsnummer in der Form 26-000 generiert ( also die erste Nummer 26-001, beim nächsten Drücken 26-002 usw.).Dabei steht 26 für das Jahr 2026 und nach dem Bindestrich die laufende Rechnungsnummer, wobei dreistellig reicht ( habe ca. 500 Rechnungen im Jahr). Diese Rechnungsnummer kopiere ich dann und füge sie auf einem anderem Arbeitsblatt entsprechenden Kunden zu.

Gleichzeitig wird bei jedem Drücken des Buttons die gerade generierte Rechnungsnummer in G4 im gleichen Arbeitsblatt ("Rechnungsnummern") in Spalte A  aufgelistet, sodass in Spalte A dann alle generierten Rechnungsnummern untereinander zu sehen sind.

Die Rechnungsnummer in D 1 eintragen brauche ich nicht, ist in dem Makro am Ende auch nur optional.

In Spalte A stehen nur Rechnungsnummern von einem Jahr.

Im neuen Jahr würde ich dann auf 27-000 ändern.

Um das Suchen geht es hier eigentlich nicht, sondern nur darum, ob die Rechnungsnummern, die in Spalte A aufgelistet werden, durch das Makro direkt im Textformat oder Standardformat in die Zelle eingetragen werden können.

Durch das Makro werden die Rechnungsnummern in Spalte A nämlich im benutzerdefinierten Format 26-000 eingetragen.

Da ich die eingetragenen Rechnungsnummern in Spalte A aber für eine spätere Suche und Zuordnung der Rechnungsnummern mit den Kunden ( INDIREKT + VERGLEICH) in anderen Arbeitsblättern benötige, sollten diese nicht im benutzerdefinierten Format gespeichert sein (zeigt dann nur Wert 1 an bei 26-001) sondern in einem Format, welches auch den Wert als 26-000 übernimmt.

Daher würde es mir völlig reichen, wenn Du mir beschreiben könntest, ob ich die Eintragungen in Spalte A über dieses Vba evt. direkt in einer Zellenformatierung  Standard oder Text (mit dem Wert 26-000) speichern könnte und nicht wie hier im Vba beschrieben.

Wow, viel Text, ich hoffe, ich konnte Dir das richtig beschreiben.

Wenn das aber nicht funktioniert ist es auch okay! Mit der Hilfsspalte und Deinem Tip ="26-"&TEXT(A1;"000") konnte ich mir schon helfen.

Vielen Dank für Deine Zeit und Deine Nachfrage

Beste Grüße

Hans-Jörg

Answer 5

Hi Hans-Jörg,

das Generieren der Rechnungsnummer aus dem letzten Eintrag in Spalte A geht nicht mehr so wie bisher in deinem Code, da die Rechnungsnummer nun ja als Text eingetragen werden soll und nicht mehr über das Zellformat als fortlaufende Ziffer. Aber du kannst die Zeilennummer als Grundlage nehmen, da es keine Leerzellen zwischendurch gibt (wie ich - denke mal - zu Recht annehme). Ich würde es so lösen:

Sub Generieren()
    Dim lngLetzte As Long
    lngLetzte = Columns(1).Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Cells(lngLetzte + 1, 1) = Right(Year(Date), 2) & "-" & Format(lngLetzte, "000")
    Range("G4") = Cells(lngLetzte + 1, 1)
End Sub

In Spalte A wird jeweils in die erste freie Zelle (beginnend ab Zeile 2) die generierte Rechnungsnummer eingetragen, ebenso in G4. Ob du die 2stellige Jahreszahl direkt aus dem aktuellen Datum extrahierst (wie in meinem Code) oder auf eine andere Art und Weise kannst du natürlich selbst entscheiden.

Bis später, Karin

Answer 6

Hallo Karin!

Vielen herzlichen Dank für Deine Mühe!

Ist wirklich eine sehr gute Alternative zu meinem bestehenden Code und den werde ich auch so übernehmen.

Vielen Dank dafür

Beste Grüße

Hans-Jörg