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

Sortieren

Forumthread: Sortieren

Sortieren
16.10.2018 10:48:57
Leonard
Hallo Forum,
mittels eines Klicks auf Zelle B1 soll eine alphabetische Sortierung nach Spalte B (A bis Z) in dem geschützten Bereich B7:G106 erfolgen. Hat jemand einen Lösungsvorschlag für mich?
Selber habe ich nicht den Hauch einer Idee.
Die Vorgehensweise mit dem „normalen“ Sortieren (Schutz aufheben, Bereich markieren / Start / Sortieren) ist mir bekannt.
Vielen Dank für eure Unterstützung
Anzeige

19
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Musterdatei?
16.10.2018 10:56:54
UweD
dann muss keiner die Datei nachbauen
AW: Musterdatei?
16.10.2018 12:04:40
UweD
Hallo
- Rechtsclick auf den Tabellenblattreiter
- Code anzeigen
- Code reincopieren
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error GoTo Fehler
    If Target.Address = "$B$1" Then
        Dim RNG As Range
        Set RNG = Range("B7:G106")
        
        ActiveSheet.Unprotect "ABC"

        With ActiveWorkbook.Worksheets("Arbeitsprozesse + Zeit").Sort
            .SortFields.Clear
            .SortFields.Add Key:=RNG.Resize(, 1), _
                SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
                :=xlSortNormal
            .SetRange RNG
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            Application.EnableEvents = False
            .Apply
        End With

        ActiveSheet.Protect "ABC"


    End If
    '*** Fehlerbehandlung 
    Err.Clear
Fehler:
    Application.EnableEvents = True
    If Err.Number <> 0 Then MsgBox "Fehler: " & _
        Err.Number & vbLf & Err.Description: Err.Clear
End Sub

LG UweD
Anzeige
AW: Musterdatei?
16.10.2018 12:57:14
Leonard
Hallo UweD,
Suuuuper 
Vielen Dank.
Nun habe ich jedoch noch einen Punkt 
Würdest Du mir bitte noch folgendes in den Code einbauen? Bei der alphabetischen Sortierung sollen nur die Zellen mit Werte in Spalte C und D untereinanderliegen.
Vielen Dank für Deine Hilfe
LG
Rentner Leonard
Anzeige
Nachfrage..
16.10.2018 13:14:58
UweD
Meinst du,?
Es soll nur sortiert werden, wenn in Spalte B was eingetragen ist?
Soll der Rest unten, wo aber Zahlen in C stehen unverändert bleiben, oder was soll damit geschehen?
AW: Nachfrage..
16.10.2018 13:59:54
Leonard
Da habe ich mich wohl sehr undeutlich ausgedrückt
Die Sortierung soll folgendes bewirken.
Die Texte in Spalte B sollen alphabetisch, was super funktioniert.
Die Texte stehen im direkten Zusammenhang mit den Werten in den nebenstehenden Zellen C bis G.
Nicht in jeder Zelle der Spalte C wird ein Wert eingetragen, jedoch in den Zellen der Spalte D.
Die alphabetische Sortierung der Spalte B sollte so erfolgen, dass der Zusammenhang zwischen den Texten und den Werten beibehalten wird.
Zum besseren Verständnis füge ich eine erweiterte Version der Datei bei.
https://www.herber.de/bbs/user/124657.xlsm
Vielen Dank für Deine Unterstützung
Anzeige
AW: Nachfrage..
16.10.2018 14:20:37
UweD
Hallo
verstehe ich noch nicht.
Der Bezug innerhalb der Zeile bleibt doch bestehen
es werden doch die ganzen Zeilen getauscht.(B bis G)
LG UweD
AW: Nachfrage..
16.10.2018 14:58:19
Leonard
Hi,
Ja, das stimmt schon und ich bin auch froh, dass Du Geduld mit mir hast.
Nur soll der Bereich B..:G.. bei dem in den Zellen der Spalte D kein Wert enthalten ist unten angestellt, und beim Sortieren nicht berücksichtigt werden.
Beispiel:
In Zelle B15 befindet sich ein Text der mit dem Buchstaben A beginnt. Nach dem Sortieren würde dieser Text in Zelle B7 stehen. Da jedoch in der Zelle D15 kein Wert enthalte ist, soll der Bereich B15:G15 beim Sortieren nicht berücksichtigt werden.
Befindet sich in der Zelle D15 ein Wert, soll der Bereich B15:G15 entsprechend seiner alphabetischen Zuordnung in der Spalte B sortiert werden.
Dieses Beispiel bezieht sich auf alle Bereiche von B7:G15 bis B106:G106.
Alle Bereiche in denen in einer Zelle der Spalte D kein Wert enthalten ist soll beim Sortieren unberücksichtigt bleiben.
Besten Dank für Deine Geduld
Ich hoffe, dass ich es jetzt etwas verständlicher beschrieben habe.
LG
Rentner Leonard
Anzeige
AW: Nachfrage..
16.10.2018 15:14:46
Leonard
Hallo UweD,
in meinem letzten Artikel habe ich mich vertippt Statt spalte D betrifft es Spalte E :-( sollte mal 'ne Pause machen :-)
Nur soll der Bereich B..:G.. bei dem in den Zellen der Spalte E kein Wert enthalten ist unten angestellt, und beim Sortieren nicht berücksichtigt werden.
Beispiel:
In Zelle B15 befindet sich ein Text der mit dem Buchstaben A beginnt. Nach dem Sortieren würde dieser Text in Zelle B7 stehen. Da jedoch in der Zelle E15 kein Wert enthalte ist, soll der Bereich B15:G15 beim Sortieren nicht berücksichtigt werden.
Befindet sich in der Zelle E15 ein Wert, soll der Bereich B15:G15 entsprechend seiner alphabetischen Zuordnung in der Spalte B sortiert werden.
Dieses Beispiel bezieht sich auf alle Bereiche von B7:G15 bis B106:G106.
Alle Bereiche in denen in einer Zelle der Spalte E kein Wert enthalten ist soll beim Sortieren unberücksichtigt bleiben.
Besten Dank für Deine Geduld
Ich hoffe, dass ich es jetzt etwas verständlicher beschrieben habe.
Anzeige
AW: Nachfrage..
16.10.2018 16:47:14
UweD
Hallo nochmal
Ich bin davon ausgegangen, dass Spalte H leer ist und als temporäre Hilfsspalte genutzt werden kann
Dort wird eine 0 gesetz, wenn E leer ist. Sonst eine 1
Zuerst wird dann nach H sortiert und dannerst nach B
zum Schluss wird die Hilfsspalte wieder geleert
Microsoft Excel Objekt Tabelle2 
Option Explicit 
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
    On Error GoTo Fehler 
    If Target.Address = "$B$1" Then 
        Dim RNG As Range, TMP As Range 
        Set RNG = Range("B7:G106") 
         
        Set TMP = Intersect(Range("H:H"), RNG.EntireRow) 'Hilfsspalte 
         
        ActiveSheet.Unprotect "ABC" 
         
        'Hilfsspalte 
        TMP.FormulaR1C1 = "=--(RC[-3]="""")" 
         
        With ActiveWorkbook.Worksheets("Arbeitsprozesse + Zeit").Sort 
            .SortFields.Clear 
            'zuerst nach Hilfsspalte filtern 
            .SortFields.Add2 Key:=TMP, _
                SortOn:=xlSortOnValues, Order:=xlAscending, _
                DataOption:=xlSortNormal 
             
            'dann nach B 
            .SortFields.Add Key:=RNG.Resize(, 1), _
                SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
                :=xlSortNormal 
             
            .SetRange Union(RNG, TMP) 
            .Header = xlNo 
            .MatchCase = False 
            .Orientation = xlTopToBottom 
            .SortMethod = xlPinYin 
            Application.EnableEvents = False 
            .Apply 
        End With 
 
        TMP.ClearContents 
        ActiveSheet.Protect "ABC" 
 
 
    End If 
    '*** Fehlerbehandlung 
    Err.Clear 
Fehler: 
    Application.EnableEvents = True 
    If Err.Number <> 0 Then MsgBox "Fehler: " & _
        Err.Number & vbLf & Err.Description: Err.Clear 
End Sub

LG UweD
Anzeige
AW: Nachfrage..
16.10.2018 18:08:21
Leonard
Hallo UweD,
vielen Dank für Deine Mühe die ich sehr schätzen weiß.
Nach dem kopieren des Codes. In Spalte H werden 0 und 1 ausgegeben, wie von Dir beschrieben.
Jedoch erhalte ich folgende Fehlermeldung…
Fehler 438
Objekt unterstützt diese Eigenschaft oder Methode nicht.
AW: Nachfrage..
16.10.2018 18:40:08
UweD
Setze mal ein‘ vor das onerror und nenne dann die Zeile, in der der Fehler auftritt.
Anzeige
AW: Nachfrage..
16.10.2018 19:11:34
Leonard
Der nachfolgende Bereich wird gelb markiert.
.SortFields.Add2 Key:=TMP, _
SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
AW: Nachfrage..
17.10.2018 08:16:17
Leonard
Hallo UweD,
hoffentlich nerve ich nicht zu sehr.
Als Rentner beschäftige ich mir Excel um meine "graue Zellen" auf trapp zu halten. Werde mir demnächst mal ein Lehrbuch zum Schreiben von Excel Codes zulegen. Welches könntest Du empfehlen?
Bis dahin benötige ich noch viel Unterstützung aus dem Forum.
Mit Filtern habe ich es auch schon versucht, dabei werden jedoch ganzen Zeilen gefiltert. Es sollen aber Spaltenbezogen sortiert werden. Und die eleganteste Lösung ist mit einem VBA-Code.
Wegen des Fehlers in dem VBA Code, habe ich mir eine andere Sortiervariante überlegt.
Wäre es eventuell einfacher mit einem Code beide Bereiche nach Zellenfarben zu sortieren?
Die Zellfarbe ist RGB(196,215,155) Hellgrün.
Wenn die Zellen D7:D106 bzw. J7:J55 nach der genannten Farbe sortiert werden, führt es zum gleichen Ergebnis das mir vorschwebt. Anzeigen aller Bereiche von B7:G106 sowie J7:M55 mit der entsprechende Farbe, und damit verbunden alle eingetragene Texte und Zahlen; wenn in den Zellen E7:E106 sowie J7:J55 Zahlen eingegeben werden.
Nur habe ich, wie Du weißt, keine Ahnung wie ein VBA Code erstellt wird.
Würdest Du mir bitte nochmal helfen dieses umzusetzen?
LG und vielen Dank für Deine Unterstützung
Rentner Leonard
Anzeige
AW: Nachfrage..
17.10.2018 08:53:15
UweD
Hallo Leonard
Bücher über VBA hab ich nie gelesen.
Ich habe mich immer den Problemen gestellt, Lösungen probiert, gegoogelt oder hier im Forum erfragt.
Kann dir also nichts empfehlen.

das filtern nach Farbe wäre so möglich.
Microsoft Excel Objekt Tabelle2 
Option Explicit 
  
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
    On Error GoTo Fehler 
    If Target.Address = "$B$1" Then 
        Dim RNG As Range 
        Set RNG = Range("B7:G106") 
 
        ActiveSheet.Unprotect "ABC" 
 
        With ActiveWorkbook.Worksheets("Arbeitsprozesse + Zeit").Sort 
            .SortFields.Clear 
 
            .SortFields.Add(RNG.Resize(, 1), xlSortOnCellColor, _
                xlAscending, , xlSortNormal).SortOnValue.Color _
                = RGB(196, 215, 155) 
            .SetRange RNG 
            .Header = xlNo 
            .MatchCase = False 
            .Orientation = xlTopToBottom 
            .SortMethod = xlPinYin 
            Application.EnableEvents = False 
            .Apply 
        End With 
 
        'ActiveSheet.Protect "ABC" 
 
    End If 
    '*** Fehlerbehandlung 
    Err.Clear 
Fehler: 
    Application.EnableEvents = True 
    If Err.Number <> 0 Then MsgBox "Fehler: " & _
        Err.Number & vbLf & Err.Description: Err.Clear 
End Sub

LG UweD
Anzeige
AW: Nachfrage..
17.10.2018 10:02:20
Leonard
Hallo UweD,
Einfach genial… es funktioniert.
Im Bereich B7:B106 ist alles mit der entsprechenden Farbe sauber sortiert. Im Bereich J7:M55 erfolgt keine Sortierung. Würdest Du bitte den Code noch entsprechend erweitern?
VBA kann ich nicht, noch nicht, und Deine Arbeit will ich nicht mit Rumspielerei zerstören.
Ich werde wohl auch den Weg nehmen den Du gewählt hattest.
Als Rentner habe ich ja die Zeit zum Probieren, Googeln, und in diesem Forum um Hilfe nachzufragen.
Vielen Dank für Deine Geduld und Hilfe.
Beste Grüße und Wünsche
Leonard
Anzeige
AW: Nachfrage..
17.10.2018 10:21:02
UweD
Hallo nochmal
in der letzten Datei von dir ist nichts in I-J vorhanden (du schreibst J-M)
habe den Teil dubliziert
Microsoft Excel Objekt Tabelle2 
Option Explicit 
   
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
    On Error GoTo Fehler 
    If Target.Address = "$B$1" Then 
        Dim RNG1 As Range, RNG2 As Range 
        Set RNG1 = Range("B7:G106") 
        Set RNG2 = Range("I7:J55") 'anpassen 
  
        ActiveSheet.Unprotect "ABC" 
  
        Application.EnableEvents = False 
         
        With ActiveWorkbook.Worksheets("Arbeitsprozesse + Zeit").Sort 
            'Bereich1 
            .SortFields.Clear 
            .SortFields.Add(RNG1.Resize(, 1), xlSortOnCellColor, _
                xlAscending, , xlSortNormal).SortOnValue.Color _
                = RGB(196, 215, 155) 
            .SetRange RNG1 
            .Header = xlNo 
            .MatchCase = False 
            .Orientation = xlTopToBottom 
            .SortMethod = xlPinYin 
            .Apply 
             
            'Bereich2 
            .SortFields.Clear 
            .SortFields.Add(RNG2.Resize(, 1), xlSortOnCellColor, _
                xlAscending, , xlSortNormal).SortOnValue.Color _
                = RGB(196, 215, 155) 
            .SetRange RNG2 
            .Header = xlNo 
            .MatchCase = False 
            .Orientation = xlTopToBottom 
            .SortMethod = xlPinYin 
            .Apply 
             
        End With 
  
        ActiveSheet.Protect "ABC" 
  
    End If 
    '*** Fehlerbehandlung 
    Err.Clear 
Fehler: 
    Application.EnableEvents = True 
    If Err.Number <> 0 Then MsgBox "Fehler: " & _
        Err.Number & vbLf & Err.Description: Err.Clear 
End Sub

LG UweD
Anzeige
AW: Nachfrage..
17.10.2018 15:25:52
Leonard
Hallo UweD,
endlich komme ich dazu - das sagt ausgerechnet ein Rentner :-) - mich nochmals ganz herzlich für Deine super tolle Arbeit / Hilfe zu bedanken. Alles funktioniert bestens.
Beste Grüße
Leonard
Prima! Danke für die Rückmeldung. owT
17.10.2018 15:29:45
UweD
;

Beliebteste Forumthreads (12 Monate)

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