Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema InputBox
BildScreenshot zu InputBox InputBox-Seite mit Beispielarbeitsmappe aufrufen

AutoFilter mit Schleife

Betrifft: AutoFilter mit Schleife von: Lizzel Mizzel
Geschrieben am: 02.11.2020 14:57:03

Hallo zusammen;

'Wartungskarten für E, M und M/E erstellen

Dim lngCriteriaCount As Long
Dim arrCriteria() As String

'Anzahl der Kriterien festlegen
lngCriteriaCount = 3

' Filterkriterien festlegen
arrCriteria(0) = "E"
arrCriteria(1) = "M"
arrCriteria(2) = "E/M"

Worksheets("Wartungskarte").ListObjects("Tabelle4").Range.AutoFilter Field:=1, Criteria1:= _
arrCriteria()

'Sortieren
With Tabelle4
Range("A29:K999").Sort _
 Key1:=Range("E" & "1"), Order1:=xlAscending, _
 Header:=xlYes
End With

Worksheets("Wartungskarte").Copy
ActiveWindow.Caption = InputBox("Nummernkreis eingeben", "Blattname")
Ich versuche mich gerade an einer Schleife. Ich habe eine Tabelle ("Wartungskarten"), die drei Kategorien hat (E, M und E/M). Ich möchte diese nun nach der Kategorie filtern, nach Intervall ("E") sortieren, als neue Mappe (nur mit E, M oder E/M) öffnen und umbenennen.
Den Filter setzen, Sortieren, Kopieren und Umbenennen habe ich in einzelnen Schritten hinbekommen. Wie kann ich das jetzt aber als Schleife gestalten? Ich scheitere daran den Filter mit den Kriterien zu füttern.

Ist das überhaupt möglich? Oder seht ihr hier noch ein "besseren" Weg?

Danke für eure Zeit/Hilfe!

Gruß lars

Betrifft: AW: AutoFilter mit Schleife
von: Lizzel Mizzel
Geschrieben am: 02.11.2020 15:43:00

Ich hab einen Vorschlag von Daniel gefunden und dementsprechend angepasst:
Dim Zelle As Range
    For Each Zelle In Range("Kriterien")
       Sheets("Tabelle4").Range("A30").AutoFilter Field:=2, Criteria1:=Zelle.Value
'Sortieren
With Tabelle4
Range("A29:K999").Sort _
 Key1:=Range("E" & "1"), Order1:=xlAscending, _
 Header:=xlYes
End With

Worksheets("Wartungskarte").Copy
ActiveWindow.Caption = InputBox("Nummernkreis eingeben", "Blattname")
Next
    Sheets("Tabelle4").Range("A30").AutoFilter Field:=2
Leider hängt er sich an der Zeile mit den "Kriterien" auf. Blatt erstellt, Tabelle gemacht. Hab auch schon einmal Worksheets("Kriterien"). davor gesetzt, aber das hat nicht geholfen.

Betrifft: AW: AutoFilter mit Schleife
von: Daniel
Geschrieben am: 02.11.2020 16:03:03

Hi

1. So wie du das schreibst, muss "Kriterien" ein Name sein, dem du über den Namensmanager einen Zellbereich zugewiesen hast.

2. in gefilterten Listen zu sortieren ist gefährlich, da ja ausgeblendet Zeilen eigentlich nicht verändert werden sollen, aber vom Sortieren betroffen sein könnten.
Daher ist es besser, vor dem Filtern und der Schleife einmal zu sortieren

3. wenn du eine With-Klammer erstellst, solltest du sie auch benutzen.

4. um die Tabelle4 anzusprechen nutzt du mal den Indexnamen und mal den Objektnamen, man kann zwar beides verwenden, sollte aber innerhalb des Codes bei einer Variante bleiben um nicht durcheinander zu kommen.

Gruß Daniel

Betrifft: AW: AutoFilter mit Schleife
von: Lizzel Mizzel
Geschrieben am: 03.11.2020 08:37:48

Hallo Daniel,

soweit umgesetzt. With Klammer setzt weiter oben schon ein.

Habe momentan drei Probleme die ich nicht gebacken bekomme:

1. Er sortiert mir nichts (meckert aber auch wegen nichts)
2. Neue Mappe wird immer neu umbenannt.
-> Mappe soll umbenannt, gespeichert und geschlossen werden und dann erst soll die nächste
geöffnet werden
-> 3. Fehler: Schreibschutz. Hab aber keinen drauf?!?!

Gruß Lars
'Wartungskarten für E, M und M/E erstellen

'*Sortieren

    Range("A29:K999").Sort _
 Key1:=Range("E" & "1"), Order1:=xlAscending, _
 Header:=xlYes
End With



'** Wartungsaufgaben in M, E und E/M sortieren, Karte kopieren und Umbennen

Dim Zelle As Range              'Für die Filterung
Dim name As String

' Tabelle filtern
For Each Zelle In Range("Kriterien")
 Sheets("Wartungskarte").Range("A30").AutoFilter Field:=1, Criteria1:=Zelle.Value
 Worksheets("Wartungskarte").Copy

' gefilterte Tabelle öffnen, umbennen, speichern und schließen
    name = InputBox("Nummernkreis eingeben", "Blattname")
    ActiveWorkbook.name = name
    ActiveWorkbook.SaveAs Filename:= _
    pfad & "\" & name, _
    FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False

    Workbooks.Close
 
Next



Betrifft: AW: AutoFilter mit Schleife
von: Daniel
Geschrieben am: 03.11.2020 09:16:02

Hi

1. beim Sortieren muss der Key innerhalb des sortierten Bereichs liegen

2. Der Workbookname kann nur durch SpeichernUnter geändert werden, nicht durch Zuweisung

3. xlNormal beim Speichern gibt es nicht mehr, du solltest dich auf den Typ (xlsx, xlsm, xlsb, xls) schon konkret festlegen

4. du willst nur das gerade gespeicherte Workbook schließen, also ActiveWorkbook.Close

5. du solltest Variablen nicht genauso benennen wie vorhandene VBA-Schlüsselwörter (Name). Das kann funktioneren, muss aber nicht. Auf jeden Fall ist der Code besser zu lesen, wenn man sofort sieht ob Variable oder VBA-Funktion gemeint ist.

Gruß Daniel

Betrifft: AW: AutoFilter mit Schleife
von: Lizzel Mizzel
Geschrieben am: 03.11.2020 15:21:59

Also, bis auf die Sortierung, konnte ich alles lösen. Danke hierfür schon mal!

Wie gesagt, nur die Sortierung will nicht so recht klappen, egal was ich mache ....

https://www.herber.de/bbs/user/141277.xlsm

Ich hoffe mir kann hier einer einen Tipp / Lösungsweg geben.
'Wartungskarten für E, M und M/E erstellen

'*Sortieren

Range("A29:K999").Sort _
 Key1:=Range("E30"), Order1:=xlAscending, _
 Header:=xlYes

End With



'** Wartungsaufgaben in M, E und E/M sortieren, Karte kopieren und Umbennen

Dim Zelle As Range              'Für die Filterung
Dim Nummernkreis As String

' Tabelle filtern
For Each Zelle In Range("Kriterien")
 Sheets("Wartungskarte").Range("B30").AutoFilter Field:=1, Criteria1:=Zelle.Value
 Worksheets("Wartungskarte").Copy
 Nummernkreis = InputBox("Bitte Nummernkreis eingeben:")
 Range("B7").Value = Nummernkreis
 
' gefilterte Tabelle öffnen, umbennen, speichern und schließen

    ActiveWorkbook.SaveAs Filename:= _
    "C:\Users\F5GDI4W\Documents\Wartungskarten\Vorlage" & "\" & Nummernkreis, _
    FileFormat:=xlOpenXMLWorkbookMacroEnabled

    ActiveWorkbook.Close
Danke!

Gruß Lars

Betrifft: AW: AutoFilter mit Schleife
von: Daniel
Geschrieben am: 03.11.2020 15:46:34

hi
erklär mir mal ein bisschen was ich tun muss, um dein Problem nachzuvollziehen.
da ich dir die Zeit, die ich für dich aufwende, dir nicht in Rechnung stellen kann, solltest du dich bemühen, diese so kurz wie möglich zu halten.
anderenfalls überlege dir, wie du dich bei mir für den Gefallen, den ich dir hier tue, revangieren kannst.
Gruß Daniel

Betrifft: AW: AutoFilter mit Schleife
von: Lizzel Mizzel
Geschrieben am: 03.11.2020 16:05:52

Ich bekomme zwar keine Fehlermeldung, aber eine Sortierung (Zahlen aufsteigend ab E30) erfolgt nicht

Betrifft: AW: AutoFilter mit Schleife
von: Daniel
Geschrieben am: 03.11.2020 16:19:15

schau dir mal die Zeilen für die Programmierung der Sortierung und die Programmierung für den Autofilter genau an.
Fällt dir ein unterschied auf?
Kleiner Tipp, es geht darum, wie und welche Zellbereiche referenzierst.
Gruß Daniel

Betrifft: AW: AutoFilter mit Schleife
von: Lizzel Mizzel
Geschrieben am: 03.11.2020 16:40:50

Sorry, aber auch mit der Info komme ich leider nicht weiter.

Setze ich ein Worksheets...vor Range bringt er mir eine Fehlermeldung.

Sortieren und Filter greifen zudem auf unterschiedliche Spalten zu (B und E).

Und sonst hab ich ja doch keine weiteren Bezüge

Gruß Lars

Betrifft: AW: AutoFilter mit Schleife
von: Daniel
Geschrieben am: 03.11.2020 19:05:48

Hi
vor welche Range(s) hast du das Worksheet genau gesetzt?
nur vor die Range vor .Sort oder auch bei der Range nach .Key1?
wie gesagt, der Key muss Bestandteil des Sortierbereichs sein und das ist er nicht, wenn er auf einem anderen Tabellenblatt liegt (ist wie bei Straßen und Städten, wenn du nur sagst "Bahnhofsstraße", dann musst du auch dazu sagen ob Hamburg oder München)
ein Range oder Cells ohne Tabellenblattangabe davor bezieht sich immer auf das aktive Tabellenblatt.
Es spielt da keine Rolle, ob es allein steht oder innerhalb einer Funktion oder Anweisung bei der du schonmal das Tabellenblatt angegeben hast. Das Tabellenblatt wird nicht übernommen sondern muss jedesmal angegeben werden.

Gruß Daniel

Betrifft: AW: AutoFilter mit Schleife
von: Lizzel Mizzel
Geschrieben am: 04.11.2020 07:55:08

Guten Morgen Daniel,

ich sag es nur ungern, aber das führt leider auch zu keinem Ergebnis. Egal ob ich ein Worksheet("Wartungskarte") vor .Sort oder vor .Rang setze oder auch vor beide, ich bekomme immer "Laufzeitfehler 1004: Die Sort Methode des Range Objekts konnte nicht ausgeführt werden".

Gruß Lars

Betrifft: AW: AutoFilter mit Schleife
von: Daniel
Geschrieben am: 04.11.2020 08:40:19

Dann machst du was anderes falsch.
Wenn du eine Fehleranalyse willst, zeige bitter an hand diner Beispieldatei, was due probiert hast.
Beschreibe bitter such, was man tun muss, um den Fehler zu erzeugen.
Sollte die Datei noch andere Makros under mehrere Module enthalten, gib bitte an, um welche Module und Makros es geht.

Gruß Daniel

Betrifft: AW: AutoFilter mit Schleife
von: Lizzel Mizzel
Geschrieben am: 04.11.2020 09:08:15

Hab es jetzt über den Makro Recorder laufen lassen und diesen direkt in der neuen Mappe ausgeführt.
Dort funktioniert es und das Makro ist wesentlich umfangreicher. Verstehen tue ich es trotzdem noch nicht.
 ActiveWorkbook.Worksheets("Wartungskarte").ListObjects("Tabelle1").Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("Wartungskarte").ListObjects("Tabelle1").Sort. _
        SortFields.Add Key:=Range("Tabelle1[[#All],[Wartungsintervall]]"), SortOn:= _
        xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Wartungskarte").ListObjects("Tabelle1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Gruß Lars

Betrifft: AW: AutoFilter mit Schleife
von: Daniel
Geschrieben am: 04.11.2020 09:18:53

Hi
hier gibst du den Key anders ein, nämlich über einen benannten Namen ("Tabelle1", hier Reprästentant für die von dir erstellte intelligente Tabelle, und nicht für das Tabellenblatt "Tabelle1", leider ist die Auswahl der Bezeichnungen hier etwas doppeldeutig)
bei Range("benannter Name") ist der Bezug auf das entsprechende Tabellenblatt in der Regel integriert und automatisch vorgegeben.
Bei Range("A1") (Zelladresse) hingegen hast du noch keinen Bezug auf ein bestimmtes Tabellenblatt und damit läuft das immer auf das aktive Tabellenblatt. Ob so ein Code funktioniert oder nicht, hängt dann immer davon ab ob das passende Tabellenblatt zufälligerweise aktiv ist oder nicht.

sollten sich die Makros jedeoch in einem Tabellenblattmodul befinden (Codes von ActiveX-Buttons), so bezieht sich eine Range mit Adresse und ohne Tabellenblattangabe immer auf das Tabellenbatt des Moduls, unabhängig davon was gerade aktiv ist.

Gruß Daniel

Betrifft: AW: AutoFilter mit Schleife
von: Lizzel Mizzel
Geschrieben am: 04.11.2020 13:10:50

Danke für die ausführliche Antwort!

Beiträge aus dem Excel-Forum zum Thema "AutoFilter mit Schleife"