Hallo zusammen,
ich habe mal wieder eine Frage.
Ich brauche ein Makro, dass die Spalte B nach Artikelnummern aufausteigend und gleichzeitig die Spalte L nach Lagerbestand aufsteigend sortiert, aber dabei leere Zellen oder Zellen mit Null in Spalte L jeweils ans Ende stellt.
Ich hoffe, Ihr könnt mir dabei helfen, da es sich um sehr viele Datensätze handelt und dieses Makro in ein größeres eingebaut werden muss.
Vielen Dank schon einmal im voraus.
LG Martina
Vorarbeit
Hallo,
ich habe keine Ahnung von Excel VBA, aber ich hab’ schon mal für Reinhard vorgearbeitet und Quicksort so umgebaut, daß es Nullen ans Ende stellt.
Gruß Rainer
PS. Getestet. 
Option Explicit
Dim Feld(0 To 10) As Integer
Private Sub Command1\_Click()
Dim i As Integer
For i = 0 To 10
Feld(i) = i
Next
QuickSort LBound(Feld), UBound(Feld)
For i = 0 To 10
List1.AddItem Feld(i)
Next
End Sub
Private Sub QuickSort(ByVal LB As Long, ByVal UB As Long)
Dim P1 As Long, P2 As Long, Ref As String, TEMP As String
P1 = LB
P2 = UB
Ref = Feld((P1 + P2) / 2)
Do
Do While ((Feld(P1) 0))
P1 = P1 + 1
Loop
Do While ((Feld(P2) \> Ref) Or (Feld(P2) = 0))
P2 = P2 - 1
Loop
If P1 P2)
If LB
Hallo Rainer,
dieser Quickshort sortiert die Liste nur bei Eingabe, oder habe ich das falsch verstanden? Wenn ich es richtig verstanden habe, kann ich das Makro leider nicht nutzen, da ich die Liste aus einem anderen System einlade und jeden Tag update. Dann lasse ich das gesamte Makro über die Liste laufen und dabei soll unter Anderem nach den oben genannten Kriterien sortiert werden.
Aber trotzdem vielen Dank für die Hilfe.
LG Martina
Hallo Martina,
dieser Quickshort sortiert die Liste nur bei Eingabe, oder
habe ich das falsch verstanden? Wenn ich es richtig verstanden
habe, kann ich das Makro leider nicht nutzen, da ich die Liste
aus einem anderen System einlade und jeden Tag update. Dann
lasse ich das gesamte Makro über die Liste laufen und dabei
soll unter Anderem nach den oben genannten Kriterien sortiert
werden.
Nein, das hat mit Eingabe nichts zu tun.
Nachdem das Reinhard in den Fingern hatte, sortiert es Deine Excel-Tabelle. Ich kenne mich mit VBA nicht aus, deshalb kann ich das nicht fertig machen. Reinhard erstezt das Array durch Deine Excel-Spalte und es tut, was Du möchtest.
Gruß Rainer
Danke, dann warte ich jetzt darauf, dass Reinhard sich meldet.
Schon einmal vielen Dank für diese Mühe. Ich werde versuchen, dass Ganze zu verstehen und vielleicht kann ich dann die Excel Spalten auch alleine einsetzen, aber im Moment habe ich noch keinen blassen Schimmer.
LG Martina
Hi,
Danke, dann warte ich jetzt darauf, dass Reinhard sich meldet.
Schon einmal vielen Dank für diese Mühe. Ich werde versuchen,
dass Ganze zu verstehen und vielleicht kann ich dann die Excel
Spalten auch alleine einsetzen, aber im Moment habe ich noch
keinen blassen Schimmer.
der Quicksort-Algorithmus ist etwas schwer zu verstehen, ist nicht von mir. Ich habe den nur an Dein Problem angepasst.
Wir können es ja mal ohne Reinhard versuchen.
Schreib doch mal einen Code, den Du so starten kannst, wie später die Sortierung gestartet werden soll und der mit einer Schleife in jede der Zellen, die sortiert werden soll, ein mal ‚hineinschaut‘ und den darin enthaltenen Wert in eine Variable schreibt, ohne dammit etwas zu tun. Bekommst Du das hin?
Gruß Rainer
Hallo Rainer,
ich habe auf der Arbeit einen Code, der die gesamte Spalte aufeinmal soritert, aber dieser Code kann nur die leeren Zellen ans Ende stellen und leider nicht die Nullen. Dieser Code ist auch schneller, als wenn er jede Zeile einzeln durchläuft. Leider habe ich ihn im Moment nicht hier und kann ihn auch so schnell nicht nachschreiben, habe ich mir aus dem Internet und einem VBA Buch gesucht. Ich kann den Code frühstens morgen einsetzen.
LG Martina
Hallo Martina,
heute Abend wirst Du den Code ja nicht mehr unbedingt brauchen.
Den vorhandenen Code so ändern, daß er die Nullen ans Ende schreibt, kann ich auf jeden Fall.
Gruß Rainer
Hallo Rainer,
okay dann melde ich mich morgen wieder im Laufe des Vormittags.
Danke und gute Nacht
LG Martina
okay dann melde ich mich morgen wieder im Laufe des
Vormittags.
Hallo Martina,
der nnachfolgende Code benutzt Excel-Funktionen um schnell zu sein und braucht dafür eine leere Hilfsspalte.
Derzeit ist das noch M, ändere das im Code ab wenn M belegt ist
Hallo Rainer,
danke für deine Vorarbeit
Gruß
Reinhard
Sub Sortierung()
Dim Zei As Long, Anz As Long
Application.ScreenUpdating = False
With Worksheets("Tabelle1")
Zei = .Cells(Rows.Count, "B").End(xlUp).Row
Zei = Application.Max(Zei, .Cells(Rows.Count, "L").End(xlUp).Row)
.Range("M2:M" & Zei).Formula = "=IF(L2\>0,1,2)"
.Range("A1:M" & Zei).Sort Key1:=.Range("M2"), Order1:=xlAscending, Header:= \_
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Anz = Application.CountIf(.Range("A1:M" & Zei), 2)
.Range("A1:M" & Zei - Anz).Sort Key1:=.Range("B2"), Order1:=xlAscending, \_
Key2:=.Range("L2"), Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, \_
MatchCase:=False, Orientation:=xlTopToBottom
.Columns("M").ClearContents
End With
Application.ScreenUpdating = True
End Sub
Guten Morgen,
Reinhard vielen Dank erstmal für Deinen Code, ich werde ihn sofort ausprobieren. Aber hier nun auch nochmal meinen Code, der auf jeden Fall schon einmal die leeren Zellen ans Ende stellt. Vielleicht kann man diesen ja auch so ändern, dass er auch die Nullen mit ans Ende stellt.
Rows(„2:65532“).Sort _
Key1:=Range(„B2“), Order1:=xlAscending, _
Key2:=Range(„L2“), Order2:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Set Zelle = Columns(„L:L“).Find(What:="", After:=[L1], LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If Not Zelle Is Nothing Then
Rows(„2:“ & Zelle.Row - 1).Sort Key1:=Range(„L2“), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End If
LG Martina
Reinhard vielen Dank erstmal für Deinen Code, ich werde ihn
sofort ausprobieren. Aber hier nun auch nochmal meinen Code,
der auf jeden Fall schon einmal die leeren Zellen ans Ende
stellt. Vielleicht kann man diesen ja auch so ändern, dass er
auch die Nullen mit ans Ende stellt.
Hallo Martina,
ich kann nicht bestätigen daß dein Code die leeren Zellen an’s Ende stellt. Getestet mit XL 2007 und XL 2000, bei XL 2000 mußte ich den unbekannten Parameter Searchformat herausnehmen.
http://www.uploadagent.de/show-181954-1327315104.html
Gruß
Reinhard
Hallo Reinhard,
bei mir hat er es gemacht, Excel 2007. Aber er stellt die leeren Zellen nicht ans Ende der Liste sondern an das Ende der jeweiligen Zahlengruppe. Soll heißen, wenn ich mehrere Lagerbestänge zu einer Artikelnummer habe, dann stellt er die Zellen mit Beständen vor die Zellen mit den Nullbeständen.
Aber Dein Makro funktioniert super. Vielen Dank dafür. Werde ich jetzt in mein Makro mit einbauen.
LG Martina
Hallo Martina,
bei mir hat er es gemacht, Excel 2007. Aber er stellt die
leeren Zellen nicht ans Ende der Liste sondern an das Ende der
jeweiligen Zahlengruppe.
in deinem makro ist noch ein Fehler oder erklär mir warum Art140 unter Art239 steht in B14:B15
Tabellenblatt: K:\[sortierung.xls]!Tabelle1
│ A │ B │ C │ D │ E │ F │ G │ H │ I │ J │ K │ L │
───┼───┼────────┼───┼───┼───┼───┼───┼───┼───┼───┼───┼─────┤
11 │ │ Art140 │ │ │ │ │ │ │ │ │ │ 409 │
───┼───┼────────┼───┼───┼───┼───┼───┼───┼───┼───┼───┼─────┤
12 │ │ Art140 │ │ │ │ │ │ │ │ │ │ 465 │
───┼───┼────────┼───┼───┼───┼───┼───┼───┼───┼───┼───┼─────┤
13 │ │ Art218 │ │ │ │ │ │ │ │ │ │ 496 │
───┼───┼────────┼───┼───┼───┼───┼───┼───┼───┼───┼───┼─────┤
14 │ │ Art239 │ │ │ │ │ │ │ │ │ │ 670 │
───┼───┼────────┼───┼───┼───┼───┼───┼───┼───┼───┼───┼─────┤
15 │ │ Art140 │ │ │ │ │ │ │ │ │ │ 942 │
───┼───┼────────┼───┼───┼───┼───┼───┼───┼───┼───┼───┼─────┤
16 │ │ Art218 │ │ │ │ │ │ │ │ │ │ 956 │
───┴───┴────────┴───┴───┴───┴───┴───┴───┴───┴───┴───┴─────┘
A11:L16
haben das Zahlenformat: Standard
Tabellendarstellung erreicht mit dem Code in FAQ:2363
Soll heißen, wenn ich mehrere
Lagerbestänge zu einer Artikelnummer habe, dann stellt er die
Zellen mit Beständen vor die Zellen mit den Nullbeständen.
Nein. das gilt nicht für Bestand =0 sondern für Bestandszelle = leer.
Gruß
Reinhard
Hallo Reinhard
in deinem makro ist noch ein Fehler oder erklär mir warum
Art140 unter Art239 steht in B14:B15Tabellenblatt: K:[sortierung.xls]!Tabelle1
│ A │ B │ C │ D │ E │ F │ G │ H │ I │ J │ K │ L │
───┼───┼────────┼───┼───┼───┼───┼───┼───┼───┼───┼───┼─────┤
11 │ │ Art140 │ │ │ │ │ │ │ │ │ │ 409 │
───┼───┼────────┼───┼───┼───┼───┼───┼───┼───┼───┼───┼─────┤
12 │ │ Art140 │ │ │ │ │ │ │ │ │ │ 465 │
───┼───┼────────┼───┼───┼───┼───┼───┼───┼───┼───┼───┼─────┤
13 │ │ Art218 │ │ │ │ │ │ │ │ │ │ 496 │
───┼───┼────────┼───┼───┼───┼───┼───┼───┼───┼───┼───┼─────┤
14 │ │ Art239 │ │ │ │ │ │ │ │ │ │ 670 │
───┼───┼────────┼───┼───┼───┼───┼───┼───┼───┼───┼───┼─────┤
15 │ │ Art140 │ │ │ │ │ │ │ │ │ │ 942 │
───┼───┼────────┼───┼───┼───┼───┼───┼───┼───┼───┼───┼─────┤
16 │ │ Art218 │ │ │ │ │ │ │ │ │ │ 956 │
───┴───┴────────┴───┴───┴───┴───┴───┴───┴───┴───┴───┴─────┘
Du hast Recht, aber erklären kann ich es nicht, habe es auch noch garnicht bemerkt gehabt. Werde jetzt Dein Makro anpassen und verwenden.
Soll heißen, wenn ich mehrere
Lagerbestänge zu einer Artikelnummer habe, dann stellt er die
Zellen mit Beständen vor die Zellen mit den Nullbeständen.Nein. das gilt nicht für Bestand =0 sondern für Bestandszelle
= leer.
Ja, dass hatte ich auch so in einer meiner Antworten geschrieben. Mit Null hatte es auch bei mir nicht funktioniert.
Vielen Dank nochmal für die schnelle Hilfe.
Gruß
Martina
Hallo Reinhard,
ich habe noch eine Frage. Hätte ich die Möglichkeit auch mein Makro so umzuschreiben, dass es funktioniert?
LG Martina
Hallo Rainer,
für Reinhard vorgearbeitet und Quicksort so umgebaut, daß es
Nullen ans Ende stellt.
danke dir.
Daß Zellen mit 0 oder leer an’s Ende sollen ist ja nur für Spalte L angesagt.
So eine Exceltabelle ist ja so geshen nur ein zweidimensionales Array.
Mit automatischem Option Base 1.
Ein beispiel für Excel bezogen auf meine Beispieltabelle:
Sub Sor()
Dim Feld, Z As Long, S As Long
Set Feld = Range("A2:L21")
For S = 2 To 12 Step 10
For Z = 1 To 20
MsgBox Feld(Z, S)
Next Z
Next S
End Sub
D.h., nach Set Feld = Range(„A2:L21“) hat Feld die gleiche Struktur
wie
Dim Feld(1 to 12,1 to 20)
Feld() muß dann zuerst nach Index 2 in der ersten Dimension sortiert werden.
Anschließend muß Feld() dann nach Index 12 in der ersten Dimension sortiert werden. Dabei das mit den Nullwerten, Leerzellen einbauen.
Für das müßte man Quick-Sort umbauen.
Da habe ich mich bislang noch nicht dran gemacht. Denn sicher ist Quicksort schnell. Aber bestimmt ist Quicksort in Vba langsamer als in einer kompilierten VB-Exe.
Und die Schnelligkeit der in Excel eingebauten Funktionen soll man nicht unterschätzen.
Ich weiß nicht in welcher Programmiersprache die geschrieben sind.
Aber wird wohl eine sehr systemnahe Sprache sein *glaub*
Gruß
Reinhard
ich habe noch eine Frage. Hätte ich die Möglichkeit auch mein
Makro so umzuschreiben, dass es funktioniert?
Hallo Martina,
klar, sodaß es dann aussieht wie meins
.Sort entspricht ja dem Daten-Sortieren in Excel.
Es sortiert nach festen Regeln, beeinflußen kann man die Regeln
in Excel durch entsprechende Häkchen oder in Vba mit den Parametern von .Sort.
Da mir nicht bekannt ist daß man da irgendwo festlegen könnte wie Nullwerte oder leere Werte zu „behandeln“ sind, muß man tricksen.
Dein „Trick“ besteht darin erst nach B zu sortieren.
Dann mit „Find“ zu ermitteln wo die oberste Zelle in L ist die leer ist. Dann wird nur oberhalb davon in L sortiert.
Irgendwas läuft da aber schief.
Mein „Trick“ besteht darin erst nach B zu sortieren.
Dann füge ich in Hilfssplate M ab M2 diese Formel ein:
=Wenn(L2>0,1,2)
Dann sortiere ich alles nach M. Alle M-Zellen mit 2 stehen dann unten.
Danach sortiere ich nur den Zellbereich nach L wo in M eine 1 steht.
Dann wird Hilfsspalte M gelöscht.
Mein Hinweis daß dein Code in B einen Fehler macht in z.B. B14/B15
ist auch so gedacht, überprüfe mal all deine mappen wo du deinen Code schon genutzt hast dementsprechend.
Zur Fehlersuche könntest du in die IF-Schleife zuoberst einbauen:
MsgBox Zelle.row
Dann den Cursor in den Code stellen und mehrmals F8 und dann immer in das Blatt schauen was sich da so tat…
Gruß
Reinhard
Nacharbeit
Hallo Rainer,
hast du noch da deine seltsam abgespechte Excelversion?
Also kannst du Code in meiner beispielmappe testen?
Wenn du Lust hast, notwendig ist es nicht, Martina hat ja funktionierenden Code.
Hier mal Beispiel-Excel-Code der demonstriert wie man einen Zellenbereich in eine Arrayvariable einlesen kann, das Array abändern kann und dann wieder nach Excel zurückschreiben.
Der wichtige „Mittelteil“ also das Array wie von mir beschrieben zu sortieren
fehlt noch, aber das ist ja reines VB *lächel*
Sub Sor()
Dim Feld, Z As Long, S As Long
Feld = Range("A2:L21")
For S = 2 To 12 Step 10
For Z = 1 To 20
Feld(Z, S) = Chr(64 + S) & Z
Next Z
Next S
Range("A2:L21") = Feld
End Sub
Wenn du so nett bist das zu codieren dann teste ich das mal in Excel 2007 mit den 12 Spalten aber 1 Mio Zeilen und messe die verbrauchte Zeit.
Ich tippe wie gesagt darauf daß mein Code, genauer die benutzung eingebauter Excelfunktionen schneller ist als eine Lösung die einen Zellenbereich in ein Array einliest, dieses im Code sortiert mit QuickSort und zurückschreibt.
Aber wissen tue ich das nicht. So oder so wäre es interessant die beiden jeweiligen laufzeiten zu wissen.
Alles nur wenn du Lust hast eine „Fingerübung“ zu machen *gg*
Gruß
Reinhard
Hallo Reinhard,
vielen Dank für die ausführliche Antwort, ich werde meine Mappen auf jeden Fall durchsuchen. Ansonsten werde ich, wie gesagt, Dein Makro auf meine Bedürfnisse anpassen.
Nochmals Danke an alle für die Hilfe.
LG Martina