Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1528to1532
1528to1532
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Bereichsnamen mit VBA erstellen und ändern

Forumthread: Bereichsnamen mit VBA erstellen und ändern

Bereichsnamen mit VBA erstellen und ändern
10.12.2016 19:02:01
Gerhard
Hallo Excelianer,
mit diesem Makro schreibe ich jeweils nach einem Kopiervorgang das aktuelle Datum in das Blatt "Protokoll".
Der Kopiervorgang soll jedes Mal protokolliert werden.
Der letzten Zelle soll ein Name zugeordnet werden, bzw. die Zuordnung des Namen soll immer an die letzte gefüllte Zelle
angepasst/geändert werden. 
Sub datum_eintragen()
Dim x
x = Worksheets("Protokoll").Cells(Rows.Count, 2).End(xlUp).Row + 1
Cells(x, 2).Value = Now
Cells(x, 3).Value = "Test"
End Sub

Die jeweils letzte Zellen sollen als Bereichsnamen zur weiteren Verwendung festgelegt werden:
(X, 2) als "x_datum" und (x, 3) als "x_text". 
Sub Makro_recorder()
With ActiveWorkbook.Names("x_datum")
.Name = "x_datum"
.RefersToR1C1 = "=Protokoll!R8C2"
.Comment = ""
End With
End Sub

Die Anpassung des aufgezeichneten Makros gelingt mir nicht.
Für den berichtigten Code bedanke ich mich im Voraus.
Gruß
Gerhard
Anzeige

3
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Bereichsnamen mit VBA erstellen und ändern
10.12.2016 19:47:46
Beverly
Hi Gerhard,
versuche es mal auf diese Weise:
Sub datum_eintragen()
Dim x
Dim namName As Name
Dim blnVorhanden As Boolean
x = Worksheets("Protokoll").Cells(Rows.Count, 2).End(xlUp).Row + 1
Cells(x, 2).Value = Now
Cells(x, 3).Value = "Test"
For Each namName In ThisWorkbook.Names
If namName.Name = "x_datum" Then
blnVorhanden = True
Exit For
End If
Next namName
If blnVorhanden = True Then
ThisWorkbook.Names("x_datum").RefersTo = "=Protokoll!" & Cells(x, 2).Address
Else
ThisWorkbook.Names.Add Name:="x_datum", RefersTo:="=Protokoll!" & Cells(x, 2).Address
End If
End Sub

Anzeige
AW: Bereichsnamen mit VBA erstellen und ändern
10.12.2016 19:51:55
Nepumuk
Hallo,
teste mal:
Public Sub Datum_eintragen()
    Dim lngRow As Long
    With Worksheets("Protokoll")
        lngRow = .Cells(.Rows.Count, 2).End(xlUp).Row + 1
        With .Cells(lngRow, 2)
            .Value = Now
            .Name = "x_datum"
        End With
        With .Cells(lngRow, 3)
            .Value = "Test"
            .Name = "x_text"
        End With
    End With
End Sub

Gruß
Nepumuk
Anzeige
AW: Danke an Beverly + Nepumuk
10.12.2016 20:24:05
Gerhard
Hallo Beverly und Nepumuk,
beide Lösungen erzeugen schnell den geünschten Namen. Ich werde diese wohl abwechselnd einsetzen ;-)
Danke und Gruß
Gerhard
Anzeige
Anzeige

Infobox / Tutorial

Bereichsnamen mit VBA erstellen und ändern

Schritt-für-Schritt-Anleitung

  1. VBA-Editor öffnen: Drücke ALT + F11, um den VBA-Editor in Excel zu öffnen.

  2. Neues Modul erstellen: Klicke mit der rechten Maustaste auf „VBAProject (dein Arbeitsbuchname)“ und wähle Einfügen > Modul.

  3. Makro erstellen: Füge das folgende Makro in das Modul ein, um einen Bereichsnamen zu definieren:

    Sub datum_eintragen()
   Dim x As Long
   x = Worksheets("Protokoll").Cells(Rows.Count, 2).End(xlUp).Row + 1
   Cells(x, 2).Value = Now
   Cells(x, 3).Value = "Test"
   Cells(x, 2).Name = "x_datum"
   Cells(x, 3).Name = "x_text"
End Sub

  4. Makro ausführen: Drücke F5, um das Makro auszuführen und den Bereichsnamen zu vergeben.

  5. Überprüfen: Gehe zu dem Namensfeld in Excel, um sicherzustellen, dass die Namen korrekt vergeben wurden.

Häufige Fehler und Lösungen

  • Fehler: „Name bereits vergeben“
    Lösung: Überprüfe, ob der Name bereits existiert. Du kannst den Namen mit ThisWorkbook.Names("x_datum").Delete vor der Neuzuweisung löschen.

  • Fehler: „Objekt nicht gefunden“
    Lösung: Stelle sicher, dass das Arbeitsblatt „Protokoll“ existiert und korrekt benannt ist.

Alternative Methoden

Eine alternative Methode zur Vergabe von Bereichsnamen ist die Verwendung der RefersToR1C1-Syntax. Hier ein Beispiel:

Sub Makro_recorder()
    With ActiveWorkbook.Names.Add(Name:="x_datum", RefersToR1C1:="=Protokoll!R" & Rows.Count & "C2")
        .Comment = "Letzte Datumseintragung"
    End With
End Sub

Diese Methode kann hilfreich sein, wenn du den Bereich flexibel anpassen möchtest.

Praktische Beispiele

  • Beispiel 1: Wenn du den Namen eines Bereichs nach der letzten gefüllten Zelle im Spaltenbereich ändern möchtest, nutze:

    Sub Namensbereich_ändern()
   Dim lastRow As Long
   lastRow = Worksheets("Protokoll").Cells(Rows.Count, 2).End(xlUp).Row
   ThisWorkbook.Names("x_datum").RefersTo = "=Protokoll!B" & lastRow
End Sub

  • Beispiel 2: Um den Bereichsnamen in einer anderen Zelle zu ändern, verwende:

    Sub Namensfeld_excel_ändern()
   ThisWorkbook.Names("x_text").RefersTo = "=Protokoll!C" & Rows.Count
End Sub

Tipps für Profis

  • Effiziente Namensvergabe: Stelle sicher, dass du die Namen konsistent und beschreibend vergibst, um Verwirrung zu vermeiden.
  • Verwendung von If-Bedingungen: Nutze If-Bedingungen, um zu überprüfen, ob ein Name bereits existiert, bevor du einen neuen Bereichsnamen vergibst. Dies verhindert Laufzeitfehler.
  • Debugging: Verwende Debug.Print, um zu prüfen, welche Werte deine Variablen annehmen, während das Makro läuft.

FAQ: Häufige Fragen

1. Wie kann ich einen bereits vergebenen Bereichsnamen ändern?
Um einen bestehenden Namen zu ändern, kannst du die RefersTo-Eigenschaft des Namensobjekts anpassen, wie in den Beispielen gezeigt.

2. Was ist der Unterschied zwischen RefersTo und RefersToR1C1?
RefersTo verwendet die A1-Notation, während RefersToR1C1 die R1C1-Notation verwendet. Letztere ist oft flexibler, wenn du mit variablen Zelladressen arbeitest.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige