Zellen-Name über alle Spalten bestimmen

20.05.2025 11:37:18

PMH

Ich möchte in einem Tabellenblatt in der kompletten Spalte jede Zelle mit einem vorhandenen Namen (wenn Name vorhanden) diesen Namen ergänzen/erweitern.
Der vorhandene Name der Zelle (z.B. mit dem Namen, z.B. 1839) soll ergänzt werden und zwar wir folgt:
ABC_1839_Test1_Text2
Jede Zelle hat einen eindeutigen Namen. Änderung des Namens, wenn vorhanden.
Wie sieht der "If" aus und wie ergänze ich den Namen? Danke schon mal vorab.

Sub ABC()
Dim cell As Range
' für jede Zelle in der Spalte
For Each cell In Selection.Columns(1).Cells
With cell
' für Zelle mit vorhandenem Namen ?? da geht's schon los: If und was dann
If .......... > "" Then
......................................
End If
End With
Next cell

End Sub

ungeprüft: Antwort KI

20.05.2025 12:18:54

MCO

Damit dein VBA-Makro jeden vorhandenen Zellnamen prüft (nicht Zellinhalt!) und ihn entsprechend ergänzt (z. B. aus 1839 wird ABC_1839_Test1_Text2), kannst du den Namen einer Zelle wie folgt überprüfen und ändern:

Hier ist der vollständige VBA-Code mit Erklärung:

Sub ABC()

    Dim cell As Range

    Dim nName As Name

    Dim oldName As String

    Dim newName As String

    Dim found As Boolean



    ' Jede Zelle in der Auswahl durchgehen (z. B. Spalte auswählen)

    For Each cell In Selection.Columns(1).Cells

        If Not IsEmpty(cell) Then

            found = False



            ' Alle definierten Namen im Workbook durchgehen

            For Each nName In ThisWorkbook.Names

                ' Prüfen, ob der Name sich auf genau diese Zelle bezieht

                If nName.RefersTo = "=" & cell.Address(External:=False) Then

                    oldName = nName.Name

                    ' Neuen Namen zusammenbauen

                    newName = "ABC_" & oldName & "_Test1_Text2"



                    ' Alten Namen löschen und neuen Namen definieren

                    nName.Delete

                    ThisWorkbook.Names.Add Name:=newName, RefersTo:=cell



                    found = True

                    Exit For

                End If

            Next nName

        End If

    Next cell



    MsgBox "Namen wurden aktualisiert."

End Sub

Was der Code macht:
Durchläuft jede Zelle in der gewählten Spalte.

Prüft, ob für diese Zelle ein benannter Name existiert.

Falls ja: Der alte Name wird gelöscht, und ein neuer zusammengesetzter Name wird vergeben.

Der neue Name hat das Format ABC__Test1_Text2.

Hinweise:
Benannte Zellen findest du im Namensmanager (Strg + F3).

Der Vergleich nName.RefersTo = "=" & cell.Address(External:=False) ist wichtig, da der Zellbezug in RefersTo als Formel gespeichert ist (z. B. =A1).

Falls zwei Namen auf dieselbe Zelle zeigen, wird nur der erste umbenannt.

AW: Zellen-Name über alle Spalten bestimmen

20.05.2025 14:36:25

Piet

Hallo

warum willst du Datein als Bilds hochladen? Macht keinen Sinn.
Probiere dafür bitte mal dieses Makro aus. Bei mir funktioniert es einwandfrei.

Amüsanter Hinweis: - für alle Kollegen!
Der menschliche Geist ist -KI- immer noch überlegen!! -- Gott sei Dank!
Während KI also löscht und mit ADD arbeitet, ändert mein Makro einfach den -alten WB-Namen- um!
Das mag die Kollegen verblüffen, scheint NICHT zu gehen, aber ich beweise, Excel kann man austricksen!

Das 1. Makro listet ALLE Wb-Namen auf, das 2 Makro NUR die Namen des gewünschten Sheet.
Bei Allen Namen wird das gewünschte Sheet mit einem "X" markiert.

Jetzt kann der Frager in Spalte E einen neuen Namen vorgeben, z.B. über Formeln zusammengesetzt, und das 3. Makro starten.
Der bereits vorhanden Wb-Namen bleibt an der alten Stelle stehen, RefersTo ändert sich ja nicht, der alte Name wird umbenannt!
Ich bin sehr gespannt auf die Rückkmeldung.

mg Piet

Sub Alle_Wbnamen_auflisten()

Dim Sht As String, Txt As String

Sht = "Tabelle1"   'Sheet, wo namen umbenannt werden sollen

With Sheets("Tabelle2")

     .Range("A2:F500").ClearContents

     For j = 1 To ThisWorkbook.Names.Count

        Txt = ThisWorkbook.Names(j).RefersTo

        .Cells(j + 2, 2) = ThisWorkbook.Names(j).Name

        .Cells(j + 2, 3) = " ' " & ThisWorkbook.Names(j).RefersTo

        If InStr(Txt, Sht) Then .Cells(j + 2, 4) = "X"

     Next j

End With

End Sub





Sub Sheet_Wbnamen_aulisten()

Dim Sht As String, Txt As String

Sht = "Tabelle1"   'Sheet, wo namen umbenannt werden sollen

With Sheets("Tabelle2")

     .Range("A2:F500").ClearContents

     For j = 1 To ThisWorkbook.Names.Count

        Txt = ThisWorkbook.Names(j).RefersTo

        If InStr(Txt, Sht) Then

           .Cells(j + 2, 2) = ThisWorkbook.Names(j).Name

           .Cells(j + 2, 3) = " ' " & ThisWorkbook.Names(j).RefersTo

        End If

     Next j

End With

End Sub







Sub Sheet_Wbnamen_umbenennen()

Dim Sht As String, Txt As String

Sht = "Tabelle1"   'Sheet, wo Namen umbenannt werden sollen

With Sheets("Tabelle2")

     For j = 1 To ThisWorkbook.Names.Count

        Txt = ThisWorkbook.Names(j).RefersTo

        If InStr(Txt, Sht) And .Cells(j + 2, 5) > "" Then _

           ThisWorkbook.Names(j).Name = .Cells(j + 2, 5)

     Next j

End With

Call Alle_Wbnamen_auflisten

End Sub

AW: Zellen-Name über alle Spalten bestimmen

20.05.2025 15:41:18

PMH

Hallo Piet,
das erste Makro listet viele Namen über die komplette Excel-Tabelle auf (also auch Auflistung von Namen der einzelnen Tabellenblätter). Aber nicht alle, sondern nur ein Teil.
Das 2. Makro zeigt keine Reaktion - ist sofort beendet.
Das 3. Makro benamst nichts um.

AW: Zellen-Name über alle Spalten bestimmen

20.05.2025 15:07:12

daniel

Hi
wenn nur bereits vorhandene Namen überarbeitet werden sollen, würde ich die Schleife über die Namen laufen lassen und prüfen, ob diese auf eine Zelle und die betroffene Spalte verweisen:

Sub NameChange()

Dim nme As Name



Dim Anfang As String

Dim Ende As String



Anfang = "ABC_"

Ende = "_Test1_Text2"



For Each nme In ThisWorkbook.Names

    On Error GoTo weiter

    If nme.RefersToRange.Cells.Count = 1 Then 'nur Namen für eine Zelle

        If nme.RefersToRange.Column = 1 Then 'nur Namen in Spalte 1

            If Not nme.Name Like Anfang & "*" & Ende Then 'prüfen, ob Name schon aktualisiert wurdee

                nme.Name = Anfang & nme.Name & Ende

            End If

        End If

    End If

weiter:

    On Error GoTo 0

Next

End Sub

sorry für das On Error Goto, aber mir fällt hier keine bessere Prüfung ein, ob ein Name auf eine Zelle verweist, den prinzipiell kann ein Name auch eine Wert enthalten.

Gruß Daniel

AW: Zellen-Name über alle Spalten bestimmen

20.05.2025 15:36:29

PMH

Hallo Daniel,
das Makro debugged bei folgender Zeile:
If nme.RefersToRange.Cells.Count = 1 Then 'nur Namen für eine Zelle

AW: Zellen-Name über alle Spalten bestimmen

20.05.2025 15:56:34

daniel

probiere es mal so:

Sub NameChange()

Dim nme As Name



Dim Anfang As String

Dim Ende As String



Anfang = "ABC_"

Ende = "_Test1_Text2"



On Error GoTo fehler

For Each nme In ThisWorkbook.Names

    If nme.RefersToRange.Cells.Count = 1 Then 'nur Namen für eine Zelle

        If nme.RefersToRange.Column = 1 Then 'nur Namen in Spalte 1

            If Not nme.Name Like Anfang & "*" & Ende Then 'prüfen, ob Name schon aktualisiert wurdee

                nme.Name = Anfang & nme.Name & Ende

            End If

        End If

    End If

weiter:

Next



Exit Sub

fehler:

Resume weiter

End Sub

AW: Zellen-Name über alle Spalten bestimmen

20.05.2025 17:08:22

PMH

siehe Absturz vorab gemeldet

AW: Zellen-Name über alle Spalten bestimmen

20.05.2025 17:22:54

daniel

lad mal die Datei mit dem Fehler hoch.
Leider befähigen einen ein paar Excel und VBA-Kenntnisse einen leider noch noch nicht zum Hellsehen.

AW: Zellen-Name über alle Spalten bestimmen

20.05.2025 15:10:09

Uduuh

Hallo,
nur vorhandene Namen auf dem aktiven Blatt in der aktiven Spalte ändern:

Sub aaa()

  Dim n As Name

  

  For Each n In Names

    If ActiveSheet Is n.RefersToRange.Parent Then

      If Not Intersect(n.RefersToRange, ActiveCell.EntireColumn) Is Nothing Then

        n.Name = "abc_" & n.Name

      End If

    End If

  Next

  

End Sub

Gruß aus'm Pott
Udo

Das ganze gibt es dann auch noch mal hier von Anfang an....

20.05.2025 17:36:24

SF

https://www.office-hilfe.com/support/threads/zellen-eintrag-in-zellen-name.59761/

AW: Zellen-Name über alle Spalten bestimmen

20.05.2025 15:51:14

PMH

Hallo Udo,
das Makro debugged bei folgender Zeile:
If ActiveSheet Is n.RefersToRange.Parent Then

AW: Zellen-Name über alle Spalten bestimmen

20.05.2025 16:25:15

PMH

Ich möchte meine Frage nochmal näher beschreiben:
bei einem Tabellenblatt einer Excel-Tabelle möchte ich den Eintrag einer Zelle (wenn dieser vorhanden)
in der kompletten Spalte A (z.B. A6 bis A1000 oder vorher per Cursor markiert) als Namen definieren.

Dabei aber den Zellen-Namen wie folgt anpassen:

Derzeitiger Eintrag (nicht Zellen-Name) als Beispiel in der Zelle wäre: "1935".
Der Zellen-Eintrag soll um einen Text davor (z.B.: ABC_) und dahinter (z.B. _xyz) ergänzt werden und dann als Zellen-Name für diese Zelle eingetragen werden, der dann über den Namensmanager auch so angezeigt wird und auch aus anderen Excel-Tabellen oder Tabellenblättern angesprochen/angesprungen werden kann.
Beispiel: ABC_1935_xyz.

AW: Zellen-Name über alle Spalten bestimmen

20.05.2025 17:04:33

daniel

Hi
kleiner Tipp
eine Beispieldatei mit deinen Daten, an denen ein Helfender Programmierer seinen Code verproben kann, vereinfacht die Sache.
Auch wäre es gut, wenn du zuerst auf gegebene Antworten reagierst, bevor du dich nochmal an die allgemeinheit wendest, das wirkt dem jeweiligen Helfer gegenüber einfach höflicher.
Gruß Daniel

AW: Zellen-Name über alle Spalten bestimmen

20.05.2025 17:30:01

Ulf

Hi,
wenn ich richtig verstanden habe willst du sowas realisieren

Option Explicit



Public Const Präfix = "ABC_"

Public Const Suffix = "_123"





Public Sub InhaltAlsName()

    Dim wb As Workbook

    Dim wks As Worksheet

    Dim rgColumn As Range

    Dim rgCell As Range

    Dim strInhalt As String

    Dim strName As String

    Set wb = ThisWorkbook

    Set wks = wb.Worksheets(1)

    Set rgColumn = wks.Columns(1)

    For Each rgCell In rgColumn.Cells

        If rgCell.Text > "" Then

            strInhalt = rgCell.Text

            strName = Präfix & strInhalt & Suffix

            wb.Names.Add Name:=strName, RefersTo:="=" & wks.Name & "!" & rgCell.Address

        End If

    Next rgCell

End Sub

hth
Ulf

AW: Zellen-Name über alle Spalten bestimmen

20.05.2025 18:22:11

daniel

was genau möchtest du jetzt:
a) bei einem vorhandenen Zellnamen den Namen um Vor- und Nachsätze erweitern
b) aus einem vorhandenen Zellinhalt für diese Zelle einen Namen anlegen

a) war das, was du in der Eingangsfrage gefragt hast.

eine Beispieldatei, in der du zeigst, was du hast und was du dir als Ergebnis wünschst, wäre sehr hilfreich.

und noch ein Tipp: eine Frage in einem weitern Forum zu stellen, obwohl ich im ersten Forum schon Menschen mit dieser Frage beschäftigen, wird von vielen Helfern als unhöflich empfunden, weil es den Eindruck vermittelt, du würdest ihre Bemühungen ignorieren und dir lieber von anderen helfen lassen als von ihnen und ihre Hilfe zurückweisen.

AW: Zellen-Name über alle Spalten bestimmen

20.05.2025 19:08:29

PMH

Ich habe in einem Tabellenblatt in der Spalte "A" in vielen Zellen (aber nicht in allen) einen Eintrag vorgenommen, z.B. "1946", in der nächsten "1950" usw.
Damit habe ich aber noch keinen Zellen-Name, sondern nur einen Zellen-Eintrag und aus diesem möchte ich einen Zellen-Namen generieren, der dann im Namensmanager steht und ich damit in der Lage bin aus anderen Tabellenblättern oder Excel-Tabellen diesen Zellen-Namen direkt anzusprechen.

Z.B. möchte ich aus dem Zelleneintrag "1946" einen Zellen-Name generieren mit Vortext und Nachtext.
Aus "1946" wird somit ein Zellen-Name: Vortext1946Nachtext

Der Vor- oder Nachtext könnte in dem Makro als Variable immer jeweils vor Ablauf geändert werden.

Das Makro müsste über die komplette Spalte "A" gehen und immer dann wenn ein Zellen-Eintrag vorhanden ist (z.B. 1946), aus diesem dann einen Zellen-Namen generieren mit Vor- und Nachtext

AW: Zellen-Name über alle Spalten bestimmen

20.05.2025 19:24:56

daniel

werden die Zellen von Hand befüllt oder stehen da Formeln drin?
ist jeder Zellinhalt nur einmal vorhanden oder kann es vorkommen, dass derselbe Zellwert mehrfach vorkommt?
wenn ja, wie soll dann verfahren werden? Bekommt nur eine Zelle den Namen, wenn ja welche oder soll der Name dann beide Zellen enthalten?
hast du inzwischen in den anderen Foren, in denen du die Frage ebenfalls schon gestellt hast, bereits antworten bekommen?
ich muss meine Zeit für dich ja nicht unnötig aufwenden.
Gruß Daniel

AW: Zellen-Name über alle Spalten bestimmen

20.05.2025 19:37:01

PMH

Hallo Daniel,
ich habe mich aus dem anderen Forum abgemeldet !

Danke für Deine bisherigen Bemühungen.

In Zelle "A5" steht eine manuell eingegebene Zahl, die nächste Zahl wird per "A5 +1" generiert (ist das auch eine Formel ?).
Jede Zahl ist einmalig.

AW: Zellen-Name über alle Spalten bestimmen

20.05.2025 19:44:58

daniel

Sub Namen_nach_Zellinhalt()



Dim Zelle As Range

Dim A As String

Dim B As String

A = "a_"

B = "_b"



For Each Zelle In Union(Range("A5"), Columns(1).SpecialCells(xlCellTypeFormulas, 1))

    ActiveWorkbook.Names.Add A & Zelle.Text & B, Zelle

Next



End Sub

beachte, dass es bestimmte Regeln für die Bezeichnung von Namen gibt. Diese müssen natürlich eingehalten werden.
ein Name darf bspw keine Leerzeichen enthalten oder einer Zelladresse entsprechen.

AW: Zellen-Name über alle Spalten bestimmen

20.05.2025 19:56:45

PMH

Hallo daniel,
ich habe Dein Makro so unverändert gleich ausprobiert.

Das Makro debugged sofort bei der u.a. Zeile und diese Zeile wird gelb hinterlegt:
ActiveWorkbook.Names.Add A & Zelle.Text & B, Zelle

AW: Zellen-Name über alle Spalten bestimmen

20.05.2025 20:31:41

daniel

dann könnte es sein, dass eine Datei etwas anders ist als du sie beschreiben hast und es deswegen notwendig wäre, dass du am Makro etwas veränderst.
ich habe hier eine Beispieldatei exakt nach deiner Beschreibung erstellt und da läuft das Makro einwandfrei, wie du hier selber feststellen kannst.
https://www.herber.de/bbs/user/177517.xlsm
Da ich deine Datei nicht kenne, kann ich dir jetzt auch nicht weiterhelfen.
Wahrscheinlich musst du schauen, wie sich deine Datei von meiner unterscheidet und dann entweder deine Datei anpassen oder das Makro.
(das das hochladen einer Beispieldatei sinnvoll ist, hatte ich dir ja schon geschrieben , warum ignorierst du das?)

AW: Zellen-Name über alle Spalten bestimmen

20.05.2025 20:55:13

PMH

Hallo daniel,
vielen, vielen Dank für Deine Unterstützung.

Das Makro läuft nun fehlerfrei durch und es macht genau das was ich will. Danke

3 Anmerkung:

der Absturz resultierte durch ein kleines "a" in einer der vielen 1.000 Zellen der Spalte A (also alphanumerisch). Ich hab's entdeckt und mein Fehler, da nicht numerisch (evtl. könnte man noch erweitern auf Abfrage numerisch)

Ich habe Range auf A6:10000" geändert, sonst ändert das Macro nur A6

es werden auch die Zeilen vor A6 geändert und Zellen-Namen angelegt, damit kann ichaber leben

AW: Zellen-Name über alle Spalten bestimmen

20.05.2025 21:21:08

Daniel

So wie ich mein Makro geschrieben habe, sollte die Zelle A5 und alle Zellen der Spalte A, die eine Formel enthalten die eine Zahl ausgibt, einen Namen erhalten.
Das war deine Beschreibung
Zellen mit Text und Zellen mit fester Zahl sollten keinen Namen erhalten (Mit Ausnahme von A5)

AW: Zellen-Name über alle Spalten bestimmen

20.05.2025 18:13:29

PMH

Hallo Ulf,
danke für die schnelle Antwort.

Das Makro debugged bei der 3. Zeile; mit der 2. Zeile habe ich die 1. Zeile zum Testen angepasst:
' strName = Präfix & strInhalt & Suffix
strName = ABC_ & strInhalt
wb.Names.Add Name:=strName, RefersTo:="=" & wks.Name & "!" & rgCell.Address

was müsste ich noch anpassen.

AW: Zellen-Name über alle Spalten bestimmen

20.05.2025 18:45:21

PMH

Hallo Ulf,
das ist eigentlich das was ich will, aber das Makro debugged bei folgender Zeile:
wb.Names.Add Name:=strName, RefersTo:="=" & wks.Name & "!" & rgCell.Address

AW: Zellen-Name über alle Spalten bestimmen

20.05.2025 17:10:36

PMH

Ich dachte ich hätte allein geantwortet, sieht zu mindestens so aus, wenn ich nach Aktualisierung mir die Übersicht anschaue.

AW: ungeprüft: Antwort KI

20.05.2025 13:22:59

PMH

Hallo MCO,
danke für die schnelle Antwort. Das Macro läuft sauber durch, aber es wird nur die eine oder andere Zelle umbenamst. Viele Zellen werden übersprungen bzw. nicht geändert.
Das Macro habe ich nur wie folgt angepaßt: newName = "ABC_" & oldName ==> & "_Test1_Text2" habe ich zum Testen erstmal weggelassen

Ich hab' mal ein Bild gemacht: 1. Bild: geändert und 2. Bild: nicht geändert.

AW: ungeprüft: Antwort KI

20.05.2025 14:03:00

PMH

Ich wollte die Dateien hochladen, aber weder jpg noch pdf wird akzeptiert

AW: ungeprüft: Antwort KI

20.05.2025 15:46:15

PMH

Nochmal zusammenfassend:
Viele Zellen, den ich einen Namen verpasst habe, werden nicht umgestellte; nur einige davon
Alle betroffenen Zellen sind gleich, bis auf den anderen eindeutigen nicht redundant vorkommenden Namen

Was könnte ich noch versuchen; es geht um die Umstellung von tausenden Namen in einem einzelnen Tabellen-Blatt innerhalb einer Excel-Tabelle.

Zellen-Name über alle Spalten bestimmen

Forumthread: Zellen-Name über alle Spalten bestimmen

29
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Zellen-Name über alle Spalten bestimmen

Forumthread: Zellen-Name über alle Spalten bestimmen

29 Beiträge zum Forumthread Beiträge zu diesem Forumthread

29
Beiträge zum Forumthread

Beiträge zu diesem Forumthread