Bereichsbezüge sind in Tabellenbl. unterschiedlich

Forumthread: Bereichsbezüge sind in Tabellenbl. unterschiedlich

sunni

Hallo alle zusammen,
ich habe für eine Arbeitsmappe Bereichsnamen vergeben, um besser rechnen zu können.
Da sich diese immer änderten (durch VBA-Löschung von Zeilen habe sie sich immer um 60 reduziert), habe ich sie über VBA nach der Zeilenlöschung neu gesetzt:
wbGesamt.Names.Add "Bereich", shGesamt.Range("F7:F1000")
Wobei wbGesamt die Arbeitsmappe und shGesamt das Tabellenblatt "Gesamt" ist und sich F7:F1000 auf dieses Tabellenblatt bezieht. Nun stelle ich erstaunt fest, dass der Bereich im anderen Tabellenblatt nur von F7:F460 angezeigt wird, wenn ich mir diesen über Einfügen/Name/Definieren anzeigen lasse (das war das Problem, warum ich mit VBA den Bereich neu gesetzt habe).
Ich dachte eigentlich immer: Bereich ist Bereich und ist für alle Tabellenblätter gleich, da es sich auf den NAMEN bezieht. Muss ich diesen nun für jedes Tabellenblatt neu definieren? Bin echt sprach- und ratlos.
Vielen Dank im voraus für die Hilfe
sunni

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Benutzer

AW: Bereichsbezüge sind in Tabellenbl. unterschiedlich

07.01.2010 14:58:32

Luschi

Hallo sunni,
die Namens-Definition mußt Du so machen, da der 2. Parameter (RefersTo) vom Typ 'Text' ist und kein Range-Bereich.
wbGesamt.Names.Add "Bereich", "=" & shGesamt.Name & "!F7:F1000"
Gruß von Luschi
aus klein-Paris

AW: Bereichsbezüge sind in Tabellenbl. unterschiedlich

07.01.2010 15:37:03

sunni

danke für deine Hilfe.
Funzt leider nicht so wirklich:
in dem 2. Tabellenblatt wird immer noch um 60 reduziert. und jetzt wird durch deine Formel im 1. Tabellenblatt immer hochgezählt:
Beim ersten Lauf von F7:F1000 auf F10:F1003 und beim 2. Lauf auf F13:F1006.
Es wird also immer um 3 Zeilen verschoben (?)).
Noch andere Ideen?

Gobale Namen - absolute/indirekte Bezüge

07.01.2010 15:16:44

NoNet

Hallo Sunni,
Du definierst den Namen "Bereich" nur für das Blatt "Gesamt" (bzw. Objektvariable shGesamt).
Durch das Löschen der Zeilen und das Neudefinieren des Namens wird dieser daher nur für dieses Blatt gesetzt. der Name ist hier GLOBAL, d.h. für alle Tabellenblätter bezieht er sich auf shGesamt !
Ich hätte folgenden Lösungsvorschlag : Name nicht global per absoluten Bezug definieren, sondern per INDIREKT(), damit gilt er jeweils für das aktuelle Blatt.
Beispiel : Test1 + NamenDefinieren1 sind wie in Deinem Beispiel, Test2 + NamenDefinieren2 wie in meinem angedachten Beispiel. Teste die Makros mal in einer neuen Mappe, der jeweilige Bereich wird zur optischen Erkennung jeweils gelb eingefärbt und mit "Test" befüllt :

Option Explicit
Sub Test1()
NamenDefinieren1
With Range("Bereich")
.Interior.Color = vbYellow 'Bereich gelb einfärben
.Value = "Test"
End With
Rows("100:159").Delete shift:=xlUp '60 Zeilen löschen
Stop 'STOP zur Kontrolle - Fortfahren mit Taste F5 im Code !
NamenDefinieren1 'Namen neu definieren
With Range("Bereich")
.Interior.Color = vbYellow 'Bereich gelb einfärben
.Value = "Test"
End With
End Sub
Sub Test2()
NamenDefinieren2
With Range("Bereich")
.Interior.Color = vbYellow 'Bereich gelb einfärben
.Value = "Test"
End With
Rows("100:159").Delete shift:=xlUp '60 Zeilen löschen
Stop 'STOP zur Kontrolle - Fortfahren mit Taste F5 im Code !
'NamenDefinieren2 - muss hier nicht neu definiert werden !
With Range("Bereich")
.Interior.Color = vbYellow 'Bereich gelb einfärben
.Value = "Test"
End With
End Sub
Sub NamenDefinieren1()
Dim wbGesamt As Workbook, shGesamt As Worksheet
Set wbGesamt = ActiveWorkbook
Set shGesamt = Worksheets("Gesamt")
'Der Name wird hier nur für das Blatt shGesamt definiert :
wbGesamt.Names.Add "Bereich", shGesamt.Range("F7:F1000")
End Sub
Sub NamenDefinieren2()
Dim wbGesamt As Workbook, shGesamt As Worksheet
Set wbGesamt = ActiveWorkbook
Set shGesamt = Worksheets("Gesamt")
'Der Name wird hier universell für jedes Blatt auf Bereich F7:F1000 definiert !
wbGesamt.Names.Add "Bereich", "=indirect(""F7:F1000"")"
End Sub

Gruß, NoNet

AW: Gobale Namen - absolute/indirekte Bezüge

07.01.2010 16:23:00

sunni

danke für deine Mühe, jetzt hab ich auch verstanden, worums geht :-)).
Leider klappts nur in Gesamt (hier steht jetzt auch "Bezieht sich auf: INDIREKT...). Hier ist mir aufgefallen, dass über Einfügen/Name/Definieren der Name des Bereiches zu sehen ist und daneben der Name des Tabellenblattes.
Komischerweise wird INDIRECT in Tabelle 2 nur bei einigen Bereichsnamen(?) übernommen. Gerade bei denen, bei denen der Name des Tabellenblattes nicht daneben stand (siehe oben).
Es sind für "Gesamt" 7 Bereiche definiert. Komischerweise hält er den Bereich (INDIREKT 7:1000) nur bei A und C. Die anderen Bereiche haben nach wie vor: "Bezieht sich auf: GESAMT 7:880 (da er schon wieder runtergezählt hat). Oder muss ich vorher noch einmal in Tabelle 2 die Bereiche neu definieren? Eigentlich doch nicht, da er sie eh neu setzt, oder?
wbGesamt.Names.Add "A", "=indirect(""R7:R1000"")"
wbGesamt.Names.Add "B", "=indirect(""T7:T1000"")"
wbGesamt.Names.Add "C", "=indirect(""S7:S1000"")"
wbGesamt.Names.Add "D", "=indirect(""F7:F1000"")"
wbGesamt.Names.Add "E", "=indirect(""D7:D1000"")"
wbGesamt.Names.Add "F", "=indirect(""E7:E1000"")"
wbGesamt.Names.Add "G", "=indirect(""Q7:Q1000"")"
Wo liegt noch der Fehler? Kann es mit dem Namen zusammenhängen?

AW: Gobale Namen - absolute/indirekte Bezüge

08.01.2010 09:02:44

sunni

ich habe die Bereiche in Tabelle 2 jetzt einfach gelöscht (sind ja in Tabelle 1 vorhanden) und schaue, was weiter passiert.
vielen Dank euch nochmal für die detaillierte Erläuterung und die Beispiele.
sunni

Beliebteste Forumthreads (12 Monate)

Bereichsbezüge sind in Tabellenbl. unterschiedlich

Forumthread: Bereichsbezüge sind in Tabellenbl. unterschiedlich

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Bereichsbezüge sind in Tabellenbl. unterschiedlich

Forumthread: Bereichsbezüge sind in Tabellenbl. unterschiedlich

5 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread