Office: Spezialfilter. Ja, mal wieder

Hallo Zusammen!

Ich brauche Eure Hilfe, beim Spezialfilter.
Momentan mache ich im Rahmen meines Maschinenbau-Studiums ein Praktikum und soll eine Vorauswahl bei Excel 'programmieren'.
Folgende Ausgangssituation (Excel 2003):

Ich habe eine Tabelle auf Tabellenblatt2 mit 70 verschiedenen Rotoren, die unterschiedliche Kriterien erfüllen. Das sind beispielsweise: Wellendurchmesser, Drehzahl und Axschub, sowie 11 andere.
Meine Aufgabe ist es jetzt die Tabelle so zu filtern, dass auf Tabellenblatt 1 Grunddaten eingegeben werden, wie Lagergröße 50mm, Drehzahl >22000 und Axschub > 4000 N und auf Tabellenblatt 2 nun nur noch die zutreffenden Rotoren.
Diese Auswahl muss dann noch sortiert werden nach dem Rotor mit dem geringsten Axschub.

Ich habe den Spezialfilter gesetzt und die Kriterien wurden auch so gesetzt, dass 'WAHR' oder 'FALSCH' das erschien. Allerdings wurde trotzdem nur nach dem ersten Kriterium gefiltert.
Ich sitze nun schon einige Zeit hierran und habe auch sämtliche google-Ergebnisse bereits durchgearbeitet, ohne Erfolg. Eine automatische Aktualisierung ist auch wie folgt eingebaut:

Private Sub Worksheet_Calculate()

Range("A2:Q68").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("A77:Q80"), _
Unique:=False

End Sub

Ich hoffe Ihr könnt mir helfen, vielen vielen Dank!

 

Hi Mario,

vielleicht solltest du mal eine ("abgespeckte") Arbeitsmappe hochladen.

Bis später,
Karin

 

So, auf Blatt 1 (Eingabe) werden die Werte manuell eingegeben.

Hier mal die Beispieldatei
Den Anhang Bsp. Selektion.xls betrachten

 

Hat keiner eine Idee?
Eventuell auch eine andere Methode, wie ein Verweis, oder ähnliches, so gut kenne ich mich nicht aus.
Ich bin dankbar für jeden Rat

 

Hi Mario,

dein Kriteriumsbereich ist falsch, er muss auch die Überschrift beinhalten - es müsste so heißen:

Code:

CriteriaRange:=Range("A12:C13"), Unique:=False

Bis später,
Karin

 

Hallo,
vielen Dank für die Antwort.
Allerdings ist es doch so, dass bei der Spezialfilterung via Formel die Kriterienüberschrift nicht identisch mit der des Filterbereichs sein darf.
Und in A12:C13 sind ja nur nocheinmal die 'Anforderungen' aus der Eingabe von Blatt 1 der Übersicht halber eingegeben.
Der Kriteriumsbereich ist A17:C17, weil nach allen Ergebnissen gefiltert werden soll, die dieses Werte mindestens erreichen und nicht nach exakt diesen Werten.

 

ein Spezialfilter benötigt immer die selben Überschriften wie der zu filternde Bereich, der Kriteriumsberich besteht also immer aus ZWEI Zeilen. Im Kriterium selbst muss die genaue Anweisung zu den zu filternden Grenzwerten stehen - also Spalte A nach =50, Spalte B nach >22000 und Spalte C nach >4500. Dein Kriteirumsbereich muss also so aussehen:


Auswahl[TABLE="width: 1"]
<colgroup><col style="font-weight:bold; width:40px;"><col style="width:97px;"><col style="width:97px;"><col style="width:97px;"></colgroup><tbody>[TR="bgcolor: #99CCFF"]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="bgcolor: #99CCFF, align: center"]16[/TD]
[TD="bgcolor: #FFFFFF, align: left"]Lagergröße[/TD]
[TD="bgcolor: #FFFFFF, align: left"]Drehzahl[/TD]
[TD="bgcolor: #FFFFFF, align: left"]Axschub[/TD]
[/TR]
[TR]
[TD="bgcolor: #99CCFF, align: center"]17[/TD]
[TD="bgcolor: #FFFFFF, align: left"]=50[/TD]
[TD="bgcolor: #FFFFFF, align: left"]>22000
[/TD]
[TD="bgcolor: #FFFFFF, align: left"]>4500[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 1"]
<tbody>[TR="bgcolor: #FFCC66"]
[TD="colspan: 3"] verwendete Formeln [/TD]
[/TR]
[TR="bgcolor: #FFCC66"]
[TD] Zelle [/TD]
[TD] Formel [/TD]
[TD] Bereich [/TD]
[/TR]
[TR]
[TD]A17 [/TD]
[TD]="="&A$13[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]B17 [/TD]
[TD]=">"&B13[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]C17 [/TD]
[TD]=">"&C13[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 1"]
<tbody>[TR="bgcolor: #FF99FF"]
[TD="colspan: 4"] definierte Namen [/TD]
[/TR]
[TR="bgcolor: #FF99FF"]
[TD] Name [/TD]
[TD] Bezieht sich auf [/TD]
[TD] Tabelle [/TD]
[TD] Z1S1-Formel [/TD]
[/TR]
[TR]
[TD]Criteria[/TD]
[TD]=Auswahl!$A$16:$C$17[/TD]
[TD]Auswahl[/TD]
[TD]=Auswahl!Z16S1:Z17S3[/TD]
[/TR]
</tbody>[/TABLE]





Dein Code für das Filterkriterium muss sich also auf die Zeilen 16 und 17 beziehen, nicht nur auf Zeile 17.

Bis später,
Karin

 

WELTKLASSE!!!
Es läuft genau so, wie ich es gerne hätte!
http://www.online-excel.de/excel/singsel.php?f=7 warum es hier komplett anders steht weis ich nicht^^

Und jetzt hätte ich noch zwei Fragen zur totalen extase (;

Mein Code laute nun so:

Private Sub Worksheet_Calculate()
Range("A2:Q68").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("A78:c79"), _
CopyToRange:=Range("A82:Q88"), _
Unique:=False
End Sub

Der Filter aktualisiert sich nun bei einer neuen Eingabe auch, allerdings nicht der Bereich, in den das Ergebnis gefiltert werden soll.
Der Bereich mit allen Werten reduziert sich, wie es sein soll, nur der Bereich, wo es hinkopiert werden soll bleibt der gleiche, wie bei vorherigen Filterung. Ich hoffe, dass da nur das Makro geändert werden muss^^

Und ist es möglich dann eine Spalte des kopierten Ergebnisses immer nach dem niedrigsten Wert zu sortieren?
Beispiel:
Nach meiner Filterung erscheinen drei Werte und ich will, dass die Zeile mit dem niedrigsten Axschub immer an erster Stelle erscheint. Ist das möglich?

Und nochmal vielen vielen Dank!!

 

lösche zuerst alle Inhalte im Kopierbereich, dann aktualisiert er sich auch. Das Sortieren kann man übrigens mit dem Makrorekorder aufzeichnen.

Ich würde nicht das Calculate-Ereignis sondern das Change-Ereignis des Eingabe-Tabellenblattes verwenden, um den Code zu starten:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim lngLetzte As Long
   Select Case Target.Address
      Case "$B$4", "$B$5", "$B$6"
         With Worksheets("Auswahl")
            lngLetzte = IIf(IsEmpty(.Cells(.Rows.Count, 7)), _
               .Cells(Rows.Count, 7).End(xlUp).Row, .Rows.Count)
            .Range("G1:J" & lngLetzte).ClearContents
            .Range("A1:D9").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range( _
              "A16:C17"), CopyToRange:=.Range("G1"), Unique:=False
            lngLetzte = IIf(IsEmpty(.Cells(.Rows.Count, 7)), _
               .Cells(Rows.Count, 7).End(xlUp).Row, .Rows.Count)
            With .Sort
               .SortFields.Clear
               .SortFields.Add Key:=Range("I2:I" & lngLetzte) _
                  , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
               .SetRange Range("G1:J" & lngLetzte)
               .Header = xlYes
               .MatchCase = False
               .Orientation = xlTopToBottom
               .SortMethod = xlPinYin
               .Apply
            End With
         End With
   End Select
End Sub

Die Bereiche musst du anpassen (gelöscht wird im Beispiel G1:J bis zur letzten belegten zeile in G, Kriteriumsbereich ist A16:C17, kopiert wird nach G1, sortiert wird G1:J bis zur letzten belegten Zeile in Spalte G).

Bis später,
Karin

 

So, ich versuche mal grade die Schritte nachzuvollziehen, da es natürlich beim ersten mal nicht so klappt, wie es das sollte^^
Case... das sind die 3 Kriterien?
die Zahl 7 sind die Zeilenanzahl im Arbeitsblatt Eingabe
G1:J ist der Bereich, in den das Ergebnis gefiltert werden soll
"I2:I" & IngLetzte ist der Bereich, der am Ende sortiert werden soll

Wenn ich das nun eingebe kommte ein Fehler. Gelb hinterlegt ist die Zeile 'With .Sort'

Hab ich das so richtig verstanden, oder gibt es deshalb den Fehler, weil ich es falsch auf meinen Fall angewandt habe?
Dankedanke

 

ich weiß leider nicht, was du meinst - bei mir wird kein Fehler angezeigt. Da ich aber die genaue Meldung des Debuggers nicht kenne, kann ich dazu auch nichts sagen.

Die Zahl 7 repräsentiert Spalte G (= Spalte 7).

Bis später,
Karin

 

Hmm, die Daten werden aber nicht aktualisiert.
Ich habe jetzt einfach mal in deine Beispieldatei noch 2 Zeilen eingebaut und es tut sich garnichts, bzw ich erhalte eine Fehlermeldung

Den Anhang Bsp. Selektion.xls betrachten

 

der Kriterienbereich ist durch das Einfügen der Zeilen jetzt nicht mehr A16:C17 sondern C18:C19 - das musst du schon im Code anpassen.

Bis später,
Karin

 

Richtig, und jetzt das berühmte aber.
Die Filterung klappt, aber nicht das Sortieren.

Laufzeitfehler '438':
Objekt unterstützt diese Eigenschaft oder Methode nicht

--> Debuggen
--> With .Sort ist gelb hinterlegt

Also 'nur' mit der Sortierung stimmt noch was nicht

 

das Sortieren funktioniert in Excel2003 anders als in Excel2010 - meine Version siehe meine Signatur. Deshalb hatte ich auch darauf hingewiesen, dass man das Sortieren mit dem Makrorekorder aufzeichnen kann.

Bis später,
Karin