VBA - Sortieren

[papi]

Guten Morgen,

ich verwende Office XP Pro.

In einer Spalte habe ich positive und negative Zahlen. Von diesen möchte ich via VBA den kleinsten und den grössten Wert der letzten 5, 10 und 15 auslesen, um deren Differenzen zu bilden. Dabei zähle ich rückwärts ab der letzten Zeile mit Wert. Um der Variablen den letzten Wert zu löschen, initialisiere ich die Variable mit dem Wert der ersten neuen Zeile (also der letzte Eintrag).

mit

Code:

 zeile = 18
a = Cells(zeile, 4).Value
b = Cells(zeile, 4).Value

For i = 1 To 10
If Cells(zeile, 4).Value < Cells(zeile - 1, 4).Value And a > Cells(zeile, 4).Value Then
a = Cells(zeile, 4).Value
End If
If Cells(zeile, 4).Value > Cells(zeile - 1, 4).Value And b < Cells(zeile, 4).Value Then
b = Cells(zeile, 4).Value
End If
Cells(zeile, 9).Value = a
Cells(zeile, 10).Value = b
zeile = zeile - 1
Next i

und spalte 4 Zeile 9 beginnend: 1 2 4 2 2 -3 -4 0 -1 -2 als Beispiel

bekomme ich aber keine korrekten Werte im höchsten bereich angezeigt. In Zeile 17 müsste eine -1 und in Zeile 13 sowie 12 eine 2 ausgegeben werden. Es wird aber in 17 -2 und 13 und 12 die 0 ausgegeben.

Wie kann man das beheben?

Schon mal im Voraus vielen Dank für die Hilfe.

[jinx]

Moinm papi,

versuch mal folgendes Vorgehen:

Code:

Dim lngCounter As Long

lngCounter = 18
a = Cells(lngCounter, 4).Value
b = Cells(lngCounter, 4).Value

For lngCounter = 18 To 9 Step -1
  If Cells(lngCounter, 4).Value < Cells(lngCounter - 1, 4).Value Then
    a = WorksheetFunction.Min(a, Cells(lngCounter, 4).Value)
  End If
  If Cells(lngCounter, 4).Value < Cells(lngCounter - 1, 4).Value Then
    b = WorksheetFunction.Max(b, Cells(lngCounter, 4).Value)
  End If
  Cells(lngCounter, 9).Value = a
  Cells(lngCounter, 10).Value = b
Next lngCounter

[papi]

vielen Dank, werde es dann noch ausprobieren.

ich habe mir dann heute auf einem anderen Weg abhilfe geschaffen. Ich habe aus der Sortierfunction einfach die beiden Werte herausgenommen

Code:

'5
Range("s5:s50").Clear
Cells(9, 18).Value = zgj
z = zg
ze2 = 21
If zgj >= 1 Then
    For i = 1 To 5
        Cells(ze2, 19).Value = Cells(z, spg).Value
    z = z - 1
    ze2 = ze2 + 1
    Next i
Range("s21:s26").Sort Key1:=Range("s21"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Cells(14, 19).Value = Cells(21, 19).Value
Cells(15, 19).Value = Cells(25, 19).Value

Im Zusammenhang mit der Sortierfunction hätte ich aber nochmal Fragen.
Wo kann ich was dazu nachlesen? Key1, Range, Order1, xlAscending...

Insbesondere dabei tauchte die Frage auf, wie kann ich auf die Ergebnisse direkt(er) zugreifen, ohne die Reihenfolge der Ursprungsdaten zu ändern?

Ich habe mir hier halt so geholfen, indem ich die entsprechenden Werte in eine Hilfszeile kopiert habe.

vielen Dank nochmals

[jinx]

Moin, papi,

mir fiel dann auch auf dem Spaziergang ein, dass man auf den Zellvergleich bei Nutzung der beiden Tabellenfunktionen verzichten könnte...

Zur weiteren Frage: bei einer benutzerdefinierten Installation wird der Objektkatalog mitinstalliert, in dem sich dann Folgendes zur Sort-Methode finden lässt:

Zitat: von VBA-Hilfe Excel97

Sortiert eine Pivot-Tabelle, einen Bereich bzw. den aktuellen Bereich (wenn nur eine Zelle angegeben wird).

Syntax

Ausdruck.Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod, IgnoreControlCharacters, IgnoreDiacritics, IgnoreKashida)

Ausdruck Erforderlich. Ein Ausdruck, der ein Range-Objekt zurückgibt.

Key1 Variant optional. Das erste Sortierfeld, als Text (ein Pivot-Feld oder Bereichsname) oder als Range-Objekt (z.*B. "Abtlg" oder Cells(1, 1)).

Order1 Variant optional. Zulässig ist eine der folgenden XlSortOrder-Konstanten: xlAscending oder xlDescending. Mit xlAscending wird Key1 in aufsteigender Reihenfolge sortiert. Mit xlDescending wird Key1 in absteigender Reihenfolge sortiert. Der Standardwert ist xlAscending.

Key2 Variant optional. Das zweite Sortierfeld, als Text (ein Pivot-Feld oder Bereichsname) oder als Range-Objekt. Falls nicht angegeben, gibt es kein zweites Sortierfeld. Wird beim Sortieren von Pivot-Tabellen nicht angewendet.

Type Variant optional. Gibt an, welche Elemente sortiert werden. Zulässig ist eine der folgenden XlSortType-Konstanten: xlSortValues oder xlSortLabels. Wird nur beim Sortieren von Pivot-Tabellen angewendet.

Order2 Variant optional. Zulässig ist eine der folgenden XlSortOrder-Konstanten: xlAscending oder xlDescending. Verwenden Sie xlAscending, um Key2 in aufsteigender Reihenfolge zu sortieren. Verwenden Sie xlDescending, um Key2 in absteigender Reihenfolge zu sortieren. Der Standardwert ist xlAscending. Wird beim Sortieren von Pivot-Tabellen nicht angewendet.

Key3 Variant optional. Das dritte Sortierfeld, entweder als Text (ein Bereichsname) oder als Range-Objekt. Falls nicht angegeben, gibt es kein drittes Sortierfeld. Wird beim Sortieren von Pivot-Tabellen nicht angewendet.

Order3 Variant optional. Zulässig ist eine der folgenden XlSortOrder-Konstanten: xlAscending oder xlDescending. Verwenden Sie xlAscending, um Key3 in aufsteigender Reihenfolge zu sortieren. Verwenden Sie xlDescending, um Key3 in absteigender Reihenfolge zu sortieren. Der Standardwert ist xlAscending. Wird beim Sortieren von Pivot-Tabellen nicht angewendet.

Header Variant optional. Legt fest, ob die erste Zeile Überschriften enthält. Zulässig ist eine der folgenden XlYesNoGuess-Konstanten: xlYes, xlNo oder xlGuess. Mit xlYes enthält die erste Zeile Überschriften (die nicht sortiert werden). Mit xlNo gibt es keine Überschriften (der gesamte Bereich wird sortiert). Mit xlGuess übernimmt Microsoft Excel die Entscheidung, ob eine Überschrift vorhanden ist und falls vorhanden, wo sie sich befindet. Der Standardwert ist xlNo. Wird beim Sortieren von Pivot-Tabellen nicht angewendet.

OrderCustom Variant optional. Ganzzahliger, bei 1 beginnender Offset für die Liste der benutzerdefinierten Sortierreihenfolgen. Falls nicht angegeben, wird 1 (Standard) verwendet.

MatchCase Variant optional. True, wenn beim Sortieren Groß- und Kleinschreibung berücksichtigt wird. False, wenn Groß- und Kleinschreibung nicht berücksichtigt wird. Wird beim Sortieren von Pivot-Tabellen nicht angewendet.

Orientation Variant optional. Hat das Argument den Wert xlTopToBottom, so wird von oben nach unten sortiert (zeilenweise). Ist das Argument xlLeftToRight, so erfolgt die Sortierung von links nach rechts (spaltenweise).

SortMethod Variant optional. Die Sortiermethode. Zulässig ist eine der folgenden XlSortMethod-Konstanten: xlSyllabary (für eine phonetische Sortierung) oder xlCodePage (für eine Sortierung nach dem Zeichensatz). Der Standardwert ist xlSyllabary.

IgnoreControlCharacters Variant optional. Wird in US/English Microsoft Excel nicht verwendet.

IgnoreDiacritics Variant optional. Wird in US/English Microsoft Excel nicht verwendet.

IgnoreKashida Variant optional. Wird in US/English Microsoft Excel nicht verwendet.

Zur Frage nach dem direkteren Zugriff: evtl.
durch Tabellenfunktionen (Max oder Large)...

[papi]

moin jinx,

vielen dank, habe wieder einiges dazugelernt. ich habe auch benutzerdefiniert die komplette installation gemacht. nur habe ich halt nur nach sort... gesucht. da wird leider die sort-methode nicht mit aufgelistet. und damit wird auch verständlich warum googeln auch nichts brachte.

ein schönes wochenende wünsche ich.

papi

[papi]

habe die erste antwort gerade mal durchprobiert.
in der zeile, wo die variable b in der schleife überprüft wird, muss natürlich das " < " durch ein " > " ersetzt werden. wollte es nur der richtigkeit halber hier reinstellen, falls jemand anderes den code auch verwenden möchte.

[jinx]

Moin, papi,

Code:

Dim lngCounter As Long

lngCounter = 18
a = Cells(lngCounter, 4).Value
b = Cells(lngCounter, 4).Value

For lngCounter = 18 To 9 Step -1
  a = WorksheetFunction.Min(a, Cells(lngCounter, 4).Value)
  b = WorksheetFunction.Max(b, Cells(lngCounter, 4).Value)
  Cells(lngCounter, 9).Value = a
  Cells(lngCounter, 10).Value = b
Next lngCounter