Zellennamen löschen und gegen normale Zellenbezeichnungen tauschen

Question

Hallo zusammen,

ich habe eine Excel-Datei, in der viele Zellen spezielle Namen haben. Diese Namen werden in Formeln und in bedingten Formatierungen verwendet.

Nun möchte ich diese Namen löschen und stattdessen die normalen Zellenbezeichnungen (A1, B2 usw.) wiederhaben. Zwar kann ich im Namensmanager die Namen einfach löschen. Allerdings verlieren dann die Formeln und bedingten Formatierungen ihren Bezug auf die jeweiligen Zellen.

Und ich möchte nicht in jede einzelne Formel gehen und dort die speziellen Namen manuell gegen die normalen Zellenbezeichnungen tauschen. Das wäre viel zu viel Arbeit.


Gibt es eine Möglichkeit, das zu automatisieren?


Danke und Gruß

Heiko1985

Answer 1

Hallo Heiko,

da ist Handarbeit angesagt.

Mir ist kein Automatismust bekannt, definierte Namen in ihre Zell- oder Bereichsadressen umzuwandeln.

Gruß
Rainer

Answer 2

Hallo,

geht nach meinen Kenntnissen nicht mit einer Automatk.

Es ist vielleicht mit "Ersetzen" zu machen, aber sehr viel Aufwand und Handarbeit. Ersetzen funktioniert aus meiner Sicht nur, wenn in der Formel der Name "XYZ" durch einen Zellennamen wie A2 ersetzt wird.

Gruß

Helmut

Answer 3

Danke für eure Antworten.

Gibt es vielleicht eine Möglichkeit, die speziellen Namen z. B. in Spalte A und den Bezugsort, also die normalen Namen in Spalte B zu exportieren. Ggf. mit einem Makro?

Danke könnte man mittels eines Makros "Suchen & Ersetzen" anwenden ...

Answer 4

Dann* könnte man mittels eines Makros "Suchen & Ersetzen" anwenden ...

Answer 5

Hallo Heiko!

In Excel2003 gibt es folgende Möglichkeit eine Liste mit den entsprechenden Verweisen zu erstellen.

Einfügen --> Namen --> Einfügen --> Liste einfügen

Dann siehst Du welcher Name sich auf welche Zelle/Bereich bezieht.
Vielleicht hilft Dir dies ein wenig.

Tom

Answer 6

Hi Heiko,

du kannst es mal mit diesem Code versuchen:

Sub NamenLoeschen()
Dim namName As Name
Dim wksTab As Worksheet
Dim rngZelle As Range
For Each wksTab In Worksheets
For Each rngZelle In wksTab.Cells.SpecialCells(xlCellTypeFormulas)
For Each namName In ActiveWorkbook.Names
If InStr(rngZelle.Formula, namName.Name) > 0 Then _
rngZelle.Formula = Application.Substitute(rngZelle.Formula, _
namName.Name, Application.Substitute(namName.RefersTo, "=", ""))
Next namName
Next rngZelle
Next wksTab
End Sub

Ich kann allerdings nicht garantieren, dass alles korrekt umgesetzt wird, denn der Code kann z.B. nicht unterscheiden, ob ein Formelteil als Bezug zu einem Bereich (also als definierter Name) oder als ein Text in "" steht. Du solltest also auf jeden Fall eine Sicherheitskoopie deiner Arbeitsmappe anlegen, bevor du den Code ausführst.

Bis später,
Karin

Answer 7

@Tomschi

In Excel2003 gibt es folgende Möglichkeit eine Liste mit den entsprechenden Verweisen zu erstellen.

Einfügen --> Namen --> Einfügen --> Liste einfügen

Dann siehst Du welcher Name sich auf welche Zelle/Bereich bezieht.
Vielleicht hilft Dir dies ein wenig.

Ja, das hilf mir sehr. Dann kann ich - wie bereits geschrieben - "Suchen & Ersetzen" unter Zuhilfename eines Makros anwenden.


@Beverly

du kannst es mal mit diesem Code versuchen:

Das hilft mir sogar noch mehr. Ich erhalte beim Ausführen zwar eine Fehlermeldung, aber das Ersetzen klappt ganz wunderbar.
Wahrscheinlich kommt die Fehlermeldung, nachdem alle Namen ersetzt wurden und es keine weiteren mehr gibt.


Danke an euch beide für die Lösung meines Problems!

Answer 8

Hi Heiko,

an welcher Stelle kommt die Fehlermeldung und was sagt der Debugger genau? Ich habe den Code bei mir getestet - ohne Fehlermeldung.

Bis später,
Karin

Answer 9

Die For-Prozedur läuft mehrere Male durch und ersetzt nach und nach die speziellen Zellennamen mit normalen. Nachdem alle Ersetzungen durchgeführt wurden (mit Einzelschrittabfrage überprüft), wird bei der Zeile

For Each rngZelle In wksTab.Cells.SpecialCells(xlCellTypeFormulas)

der Fehler

Laufzeitfehler '1004':
Keine Zellen gefunden.

angezeigt.

Aber wie gesagt: Das macht nichts. Das Ersetzen funktioniert, und ich bin zufrieden. :)


Ich habe übrigens schon mehrfach versucht, den Link "Diese Antwort hat mein Problem gelöst" unter Deinem Post zu betätigen. Leider klappt das nicht. :(

Answer 10

Hi Heiko,

der Fehler tritt auf, wenn es auf einem Tabellenblatt keine Formeln gibt.

Bis später,
Karin