Microsoft Excel

Herbers Excel/VBA-Archiv

Bereichsbezüge sind in Tabellenbl. unterschiedlich | Herbers Excel-Forum


Betrifft: Bereichsbezüge sind in Tabellenbl. unterschiedlich von: sunni
Geschrieben am: 07.01.2010 14:29:12

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

  

Betrifft: AW: Bereichsbezüge sind in Tabellenbl. unterschiedlich von: Luschi
Geschrieben am: 07.01.2010 14:58:32

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


  

Betrifft: AW: Bereichsbezüge sind in Tabellenbl. unterschiedlich von: sunni
Geschrieben am: 07.01.2010 15:37:03

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??


  

Betrifft: Gobale Namen - absolute/indirekte Bezüge von: NoNet
Geschrieben am: 07.01.2010 15:16:44

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


  

Betrifft: AW: Gobale Namen - absolute/indirekte Bezüge von: sunni
Geschrieben am: 07.01.2010 16:23:00

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?


  

Betrifft: AW: Gobale Namen - absolute/indirekte Bezüge von: sunni
Geschrieben am: 08.01.2010 09:02:44

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


Beiträge aus den Excel-Beispielen zum Thema "Bereichsbezüge sind in Tabellenbl. unterschiedlich"