Microsoft Excel

Herbers Excel/VBA-Archiv

Spalten mit Formeln automatisch sortieren | Herbers Excel-Forum


Betrifft: Spalten mit Formeln automatisch sortieren von: Murino
Geschrieben am: 25.01.2010 19:27:40

Hallo Leute,
habe wieder ein Problem. Den ganzen Tag wühle ich in den Foren rum, das passende habe ich
nicht gefunden. Meine große Bitte, schaut mal ob Ihr eine Lösung findet.
PROBLEM:
Ich habe drei Spalten „A“-„B“-„C“, diese drei Spalten haben Werte die über Formeln bezogen werden, in der mittleren Spalte sind Uhrzeiten (Reservierungen) eingetragen. Nun soll die Spalte mit den Reservierungen sortiert werden, und zwar so dass die früheste Zeit oben erscheint, die restlichen Spalten müssen sich nach den Reservierungen anpassen.
Das Problem ist dass Formeln sich nicht sortieren lassen, ich habe es über einen Makro probiert „Kopieren und als Werte einfügen“, es funktioniert zwar, aber es werden die Werte nach unten geschoben und es bilden sich lange leere Zellen. Wie bekomme ich eine ganz simple Sortierung hin, möglichst direkt bei der Eingabe und vielleicht sogar mit Formeln.

Danke im Voraus
Gruß an alle
Ciao
Aki

  

Betrifft: AW: Spalten mit Formeln automatisch sortieren von: Tino
Geschrieben am: 25.01.2010 20:05:06

Hallo,
wenn Du die Formeln behalten willst geht es nach meiner Meinung am einfachsten über
eine zweite Tabelle wo die Daten ohne Formel eingefügt werden.

Nur mal so ein Ansatz, kommt als Code in die Eingabetabelle.

Private Sub Worksheet_Change(ByVal Target As Range)
With Tabelle2
    .UsedRange.ClearContents
    .Range(Tabelle1.UsedRange.Address) = Tabelle1.UsedRange.Value
    .UsedRange.Sort Key1:=.Cells(1, 2), Order1:=xlAscending, Header:=xlYes
End With
End Sub


Gruß Tino


  

Betrifft: nur so eine Idee?! nicht ausgiebig getestet. von: Tino
Geschrieben am: 25.01.2010 20:18:12

Hallo,
Du könntest mal dies noch ausgiebig testen.
Ist mir nur so eingefallen ob es richtig funktioniert habe ich nicht ausgiebig getestet.

Hier wird die Berechnung auf der Tabelle abgestellt.
Bei einer Eingabe kurz aktiviert um die Formeln zu aktualisieren und
danach wieder Berechnung aus danach wird sortiert.

Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveSheet
 .EnableCalculation = False
  Application.Calculate
 .EnableCalculation = True
 .UsedRange.Sort Key1:=.Cells(1, 2), Order1:=xlAscending, Header:=xlYes
End With
End Sub
Gruß Tino


  

Betrifft: AW: nur so eine Idee?! nicht ausgiebig getestet. von: Murino
Geschrieben am: 25.01.2010 21:57:35

Hallo Tino,
danke für deine Hilfe,
ich habe den code eingegeben, tut sich aber nix.

Grüße
aki


  

Betrifft: hier mein Beispiel dazu von: Tino
Geschrieben am: 25.01.2010 22:13:54

Hallo,
normalerweise müsste in Spalte B der größte Wert oben stehen
dies wird durch abstellen der Berechnung verhindert.
Dies macht zwar im Bsp. so keinen Sinn, aber es soll ja nur als Beispiel dienen.

https://www.herber.de/bbs/user/67484.xls

Gruß Tino


  

Betrifft: AW: hier mein Beispiel dazu von: Murino
Geschrieben am: 26.01.2010 09:01:02

Hallo Tino,
danke noch mal. irgendwie versteh ich den Zusammenhang nicht, wo kommen die Werte her?

Ich habe inzwischen ein anderes Makro herausbekommen, allerdings müsste ich noch 2 weitere im gleichen Blatt einfügen, weißt du eventuell wie man mehrere von diesen Makros
rein bekommt.
Danke im Voraus
Gruß
Aki

Private Sub Worksheet_Activate()
Range("A1:D33").Select
Selection.Sort Key1:=Range("C1"), Order1:=xlDescending, Header _
:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub



  

Betrifft: AW: hier mein Beispiel dazu von: Tino
Geschrieben am: 26.01.2010 10:04:07

Hallo,
wie wo kommen die Werte her?
Dies ist doch eine Formel die als Beispiel die Werte aus Spalte A holt.
Normalerweise ist die Funktion in Spalte B so, dass der größte wert oben steht.

Dein Makro kann Dein Problem so nicht lösen,
weil nach dem Sortieren die Formel neu berechnet werden und
dadurch je nach Formel die Sortierung wieder zunichte gemacht werden kann.

Gruß Tino


  

Betrifft: AW: hier mein Beispiel dazu von: Murino
Geschrieben am: 27.01.2010 00:29:29

Hallo Tino,
Danke für deine Hilfe,
ich muss leider aufgeben. Ich bin zu d. dafür. Ich habe alles probiert mit Makro Rekorder, kopieren und als Wert einfügen, alle Foren durchgestöbert "nothing". Ich bekomme es nicht geregelt dass bei "aufsteigend sortieren", die Leerzeilen oben schwimmen.
Ich bin deprimiert.
Gute Nacht, hab kein Bock mehr.

Liebe Grüße
Aki






  

Betrifft: gibst Du immer so schnell auf? von: Tino
Geschrieben am: 27.01.2010 11:21:03

Hallo,
wie wäre es mit einer Beispieldatei, vielleicht fällt mir dann etwas ein dazu.

Gruß Tino


  

Betrifft: AW: gibst Du immer so schnell auf? von: Murino
Geschrieben am: 27.01.2010 13:06:50

Hallo Tino,
vielen danke für dein Angebot ist sehr lieb von dir,
ich raffe nichts mehr stecke in eine Sackgasse.
Ich habe dir eine Musterdatei hochgeladen. Die eigentliche Sortierung „Aufsteigend“ (kleinste Werte Oben) sollen im Blatt Beispiel „Zeit“ erfolgen. Das Problem dass ich habe ist, das die leere Zeilen oben schwimmen, möchte ich aber nicht, die sollen gefällig unten bleiben.
Es wäre kein Problem wenn die Quell-Datei sortiert werden müsste, dass geht leider nicht denn di Daten werden aus diversen Masken eingegeben.

Ich danke dir sehr für dein Engagement
Liebe Grüße
Achille


https://www.herber.de/bbs/user/67544.xls


  

Betrifft: AW: gibst Du immer so schnell auf? von: Tino
Geschrieben am: 27.01.2010 14:57:19

Hallo,
ok. so geht es mit einer Hilfsspalte die wieder gelöscht wird.
Die Sortierung wird vorgenommen sobald in
Tabelle Eingabe im Bereich F3:K32 etwas eingegeben wird.

https://www.herber.de/bbs/user/67556.xls

Gruß Tino


  

Betrifft: AW: gibst Du immer so schnell auf? von: Murino
Geschrieben am: 27.01.2010 17:57:16

Hallo Tino,
vielen Dank für deine Mühe,
Ich weiß es wirklich zu schätzen.
Leider funktioniert dass nicht so! Du musst ja bedenken dass die Werte als Verknüpfung ankommen (Formeln). Ich habe das mal bei der Mustertabelle simuliert und schon klappt es nicht mehr. Die Quelldatei darf nicht sortiert werden, weil es einfach nicht möglich ist, die Eingabemasken wo die Reservierungen eingegeben werden sind auf Verschiedene Blätter verteilt.

Nochmals Danke für deine Hilfsbereitschaft und Mühe

Schöne grüße
Achille

Ps Du hast ein Südländischen Namen, bist du etwa Italiener?

https://www.herber.de/bbs/user/67563.xls


  

Betrifft: AW: gibst Du immer so schnell auf? von: Tino
Geschrieben am: 27.01.2010 19:51:20

Hallo,
die Quelle Eingabe wird doch gar nicht sortiert sondern die Tabelle Beispiel?
Schalten wir mal auf der Tabelle2 "Beispiel" die Berechnung noch ab,
diese wird nur bei Eingabe in Tabelle1 kurz ausgeführt und danach die Daten in Tabelle2 sortiert.

Ersetze den Code im Modul durch diesen.

Sub SortiereBereiche()
Dim rngBereich As Range
Dim iCalc As Integer
With Application
     iCalc = .Calculation
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
    
    With Tabelle5
      .EnableCalculation = True
      Application.Calculate
      .EnableCalculation = False
      'Bereich Abends
      Set rngBereich = .Range("A2", .Cells(.Rows.Count, 1).End(xlUp).Offset(0, 4))
      rngBereich.Columns(4).FormulaR1C1 = "=IF(RC[-2]<>"""",RC[-2],10^-307)"
      rngBereich.Sort Key1:=rngBereich.Cells(1, 4), Order1:=xlDescending, Header:=xlYes
      rngBereich.Columns(4).ClearContents
      
      'Bereich Mittag
      Set rngBereich = .Range("E2", .Cells(.Rows.Count, 5).End(xlUp).Offset(0, 4))
      rngBereich.Columns(4).FormulaR1C1 = "=IF(RC[-2]<>"""",RC[-2],10^-307)"
      rngBereich.Sort Key1:=rngBereich.Cells(1, 4), Order1:=xlDescending, Header:=xlYes
      rngBereich.Columns(4).ClearContents
    End With
    
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = iCalc
End With
End Sub
Gruß Tino


  

Betrifft: AW: gibst Du immer so schnell auf? von: Murino
Geschrieben am: 28.01.2010 13:20:15

Hallo Tino,
entschuldige bitte noch einmal,
die absteigende Sortierung ist nicht das Problem, dass macht Excel sowieso alleine.
Die aufsteigende Sortierung macht mir Kopfzerbrechen.
Es funktioniert einfach nicht. Versuche mal in Tabelle „Eingabe“ etwas einzugeben,
ew. eine Eingabe über eine Verknüpfung, es findet keine aufsteigende Sortierung statt.

Ich habe die Datei noch mal hochgeladen!

Nochmals Danke
Schöne Grüße
Achille
https://www.herber.de/bbs/user/67589.xls


  

Betrifft: ok. nochmal von: Tino
Geschrieben am: 28.01.2010 14:39:54

Hallo,

jetzt funktioniert es aber.

https://www.herber.de/bbs/user/67593.xls

Gruß Tino


  

Betrifft: AW: ok. nochmal von: Murino
Geschrieben am: 28.01.2010 18:14:42

Hallo Tino,
geht nicht, probier selbst nocheinmal,
es ist mir einfach zu Peinlich, darum bitte ich dich die Sache ruhen zu lassen.
Vielleicht ist mein Excel falsch eingestellt, oder wer weis der Kuckuck.
Vielleicht ist es mit eine Formelfunktion zu lösen „sverweis“ oder sonstiges.

Vielen dank für die Mühe und deine Geduld
Liebe grüße
Achille


  

Betrifft: AW: ok. nochmal von: Tino
Geschrieben am: 28.01.2010 19:43:06

Hallo,
habe es getestet, es wird richtig absteigend Sortiert.



Für eine Formel, schau dich mal hier um.
http://www.excelformeln.de/formeln.html?gruppe=11

Gruß Tino


  

Betrifft: meinte aufsteigend ;-) oT. von: Tino
Geschrieben am: 28.01.2010 19:52:02




Beiträge aus den Excel-Beispielen zum Thema "Spalten mit Formeln automatisch sortieren"