Office: VBA - Syntax mehrere Bereiche sortieren

Hallo,

gegeben sind 3 Bereiche im Blatt "Stammdaten":

In Zeile 1 stehen jeweils die entsprechenden Überschriften.

Bisher habe ich diese Bereiche im Namensmanager manuell festgelegt und folgendermaßen nach den Spalten D und F sortiert:

Code:

Public Sub Lieferanten_Sortieren()
    
    With ThisWorkbook.Worksheets("Stammdaten")
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:= _
            .Range("D:D"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:= _
            .Range("F:F"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With .Sort
                .SetRange Range("D:F")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
    End With
    
End Sub

Jetzt habe ich mir aber eine Prozedur geschrieben, in welcher die Bereiche automatisch festgelegt werden:

Code:

Public Sub BereicheNeuDefinieren()
    
    With ThisWorkbook.Worksheets("Stammdaten")
        
        ' Bereich "Lieferant" neu definieren
        ' =Stammdaten!$D$2:$D$2000
        .Names("Lieferant").RefersToR1C1 = "=Stammdaten!R2C4:R2000C4"
        
        ' Bereich "LieferantLand" neu definieren
        ' =Stammdaten!$E$2:$E$2000
        .Names("LieferantLand").RefersToR1C1 = "=Stammdaten!R2C5:R2000C5"
        
        ' Bereich "Lieferantvon" neu definieren
        ' =Stammdaten!$F$2:$F$2000
        .Names("Lieferantvon").RefersToR1C1 = "=Stammdaten!R2C6:R2000C6"
        
    End With
    
End Sub

Gesucht:

Da ich ja nun zentral an einer Stelle alle Bereiche anpassen/einstellen kann falls es nötig werden sollte, bzw. sie sogar dynamisch generieren könnte, wie sieht es da mit dem Sortieren aus?
Besteht die Möglichkeit beim Sortieren anstatt der Spalten die Bereichsnamen anzugeben?
Und wie sähe die Syntax aus wenn ich drei Bereiche gleichzeitig nach den Werten von zwei Bereichen sortieren möchte?


Ich hoffe ich habe mich verständlich ausgedrückt.
Und danke für Eure Zeit.

 

Manchmal ist die Lösung wohl zu einfach um darauf zu kommen...

Anstatt der Spalten einfach den Bereichsnamen einsetzen,
und (in meinem Fall wichtig)

Code:

.Header = xlYes

auf

Code:

.Header = xlNo

ändern.


Das Ergebnis sieht dann so aus:

Code:

Public Sub Lieferanten_Sortieren()
   
    With ThisWorkbook.Worksheets("Stammdaten")
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:= _
            .Range("Lieferant"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:= _
            .Range("Lieferantvon"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With .Sort
                .SetRange Range("Lieferant:Lieferantvon")
                .Header = xlNo
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
    End With
   
End Sub

 

Bei der Gelegenheit, vielleicht kann mir ja jemand folgendes beantworten:

Was bewirken folgende Befehle aus dem Sortier-Code?

Code:

DataOption:=xlSortNormal

.MatchCase = False

.SortMethod = xlPinYin

.Apply

 

Grüezi Achim

Für alle 4 Begriffe:

Setze den Schribcursor im VBA-Esitor in das Wort und drücke F1.... ;-)

Es handelt sich allesamt um einzelne Sortier-Parameter, die enstprechend eingestellt werden können.

 

Hallo Thomas,

danke für Deinen Tipp.
Da bei mir Zuhause die Hilfe nicht klappt, vergesse ich immerwieder das es sie gibt... ;O)
(hab jetzt in der Arbeit geschaut)




Habe noch eine Frage:

In meiner Testmappe hat alles wunder geklappt, im Projekt kam beim Sortieren dann die Fehlermeldung "... für Objekt Global fehlgeschlagen "
(ich glaube die Fehlernummer war 1004)

Nun waren meine Namen im Namensmanager immer für das entsprechende Blatt festgelegt.
Habe die Namen gelöscht, neu angelegt, aber diesmal für den Bereich "Arbeitsmappe".
Und schon funktionierts.
Allerdings muss dann noch:

Code:

Public Sub BereicheNeuDefinieren()
   
    With ThisWorkbook.Worksheets("Stammdaten") 

in

Code:

Public Sub BereicheNeuDefinieren()
   
    With ThisWorkbook

geändert werden.

Hat jemand ne Ahnung/kann mir erklären warum?
Die Namen beziehen zich doch weiterhin auf bestimmte Bereiche in bestimmten Blättern...


Falls Ihr nicht ganz folgen könnt, mache ich auch gerne Screenshots.

 

Grüezi Achim

Bereichsnamen gibt es seit jeher auf zwei Ebenen - einmal auf Arbeitsmappen-Ebene und einmal auf Tabellenblatt-Ebene. Diese können sich durchas auch auf dieselben Bereiche beziehen.

Festgelegt werden sie dann eben auch auf Mappen- oder Tabellen-Ebene.

Gibt es zwei gleichnamige Bereiche so hat im Tabellenblatt der lokale Name Vorrang.


Diese Unterschiede werden in der VBA-Umgebung etwas deutlicher als wenn die Namen im Tabellenblatt für Berechnungen verwendet werden.

 

Hallo Thomas,

danke für Deine Erklärung.

Aber warum ich über VBA einen Bereichsnamen auf Tabellenblattebene nicht dimensionieren kann, auf Arbeitsmappenebene aber schon, das verstehe ich immer noch nicht.

Oder, ich müsste irgendwo den Code entsprechend anders schreiben, die Frage ist nur wo und wie?


Im Moment klappt ja alles wie es soll, ich würds halt nur gerne verstehen.

Wenn Du zum Erklären eine Beispielmappe oder sonstwas brauchst, lade ich gerne heute Abend hoch was Du möchtest.

 

Grüezi Achim

Mit VBA ist das IMO eigentlich recht klar:

ThisWorkbook.Names sind die globalen Namen

ThisWorkbook.Worksheets("EinName").Names sind die lokalen Namen.


Aber generell solltest Du mit dem Namens-Manager arbeiten, der macht dies dann wesentlich einfacher und durchsichtiger:

http://www.jkp-ads.com/officemarketplacenm-en.asp

 

Hallo Thomas,

danke für Deine Erklärungen und den Link.
(werde das Teil in der Firma vermutlich nur nicht nutzen können/dürfen)

Ich habe mir mal eine Beispielmappe gebastelt um die Zusammenhänge zu verstehen.


Ergebnis:

Auf lokale Namen kann ich nur zugreifen, wenn das entsprechende Blatt auch aktiviert ist.

Bsp.:
Wenn ich einen lokalen Bereich sortieren möchte, muss das entsprechende Blatt aktiv sein.
Im Unterschied dazu kann ich aber mit ".Range" oder ".cells" von überall her auf die entsprechenden Zellen zugreifen.
Da muss das entsprechende Blatt nicht aktiviert werden.
Der exakt gleiche Bereich, nur global erzeugt, lässt sich aber von jedem Blatt aus sortieren.


Ausnahme:
Erzeugen, dimensionieren und löschen kann ich lokale wie globale Namen gleichermaßen von überall aus,
dafür muss kein bestimmtes Blatt aktiviert sein.
(Die lokalen Namen werden sogar in "ThisWorkbook.Names.Count" angesprochen.)
Ich vermute, dies geht sogar aus einer anderen Mappe heraus, habe ich aber noch nicht getestet.
Und möglicherweise gibt es auch noch mehr "Ausnahmen".
Wenn jemand eine weiß, wärs nett sie zu posten.


Fazit:
Ich sehe wohl einen Sinn in der Unterscheidung zwischen lokalen und globalen Nahmen.
In den "Zugriffsbeschränkungen" sehe ich aber keinen Sinn, diese Logik erschließt sich mir nicht.
(muss ich mir halt merken)


Auf jeden Fall habe ich wieder was gelernt.

 

Grüezi Achim

Nicht ganz... ;-)

Wenn Du dem lokal angelegten namen auch das Tabellenblatt als Referenz mitgibst, klappt das Sortieren auch auf dem nicht aktiven Tabellenblatt:


With .Sort
.SetRange ThisWorkbook.Worksheets("Tabelle2").Range("Bereich4")
.Header = xlNo

 

Hallo Thomas,

auf die Gefahr das ich mich wiederhole:

Manchmal ist die Lösung wohl zu einfach um darauf zu kommen...

*grmpf*


Lieben Dank für Deine Tipps.