Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1692to1696
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

mit Userform suchen und filtern

mit Userform suchen und filtern
25.05.2019 07:24:49
Markus
Hallo zusammen,
die Schwierigkeit für mich steigt..
Ich möchte mit einer Userform eine Tabelle „auswerten“. Durch Filtern der Tabelle werden die Zeitbereich von erstellten Rechnungen bereits mit zugehörigen Inhalt in der Listbox der Userform angezeigt. Nun möchte in meiner Userform auch noch nach Kunden, oder Projektnummer oder Kundennummer suchen/ filtern im entsprechenden Datumsbereich.
Für eine andere Userform wurde mir hier im Forum mal ein Code vorgegeben, der da auch ganz wunderbar funktioniert (beim Button „Suchen“ hinterlegt…). Dieser Code bezieht sich zwar auf das gleiche Tabellenblatt allerdings wie schon gesagt in einer anderen Userform. Diese hatte ich mal versucht entsprechend zu erweitern, allerdings steigt der Code bei einer Anzahl von 9 aus…
In der aktuellen Userform bekomme ich diesen erst gar nicht zum laufen. Kann mir von euch jemand weiterhelfen? Oder gibt es eine einfachere Möglichkeit?
Der große Vorteil an dem Code (da wo er funktioniert) ist, ich geben nur einzelne Zeichen ein und er sucht alle passenden Werte mit entsprechendem Inhalt.
Nachteil: Für mich als Einsteiger ganz schön kompliziert…daher auch schwer auf eine andere Nutzung zu übertragen…
https://www.herber.de/bbs/user/130013.xlsm
Gruß Markus

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: mit Userform suchen und filtern
25.05.2019 09:02:41
Matthias
Hallo
Nur mal so nebenbei.
Lass mal alle Objekte leer und klicke auf Suchen
TextBox5 gibt es nicht!
Gruß Matthias
AW: mit Userform suchen und filtern
25.05.2019 11:46:37
Markus
Hallo Matthias,
danke für Deinen Hinweis. Nach Ergänzen der Textbox5 läuft die Suche.
Wie ich schon zuvor geschrieben habe lässt sich der Code bis zu 8 Spalten erweitern bei anzahl=9 steigt er aus. Für mich wären jedoch die Spalten 1,3,4,5,11,12 und 13 für die Ausgabe in der Listbox von Bedeutung. Hat hierzu noch jemand eine Idee?
@ Hajo: Danke auch für deinen Hinweis. Ein Programmierer wurde das wohl machen, aber genau das bin ich nicht. Daher bin ich mir auch nicht über die Konsequenzen im klaren.
Gruß
Markus
https://www.herber.de/bbs/user/130015.xlsm
Anzeige
AW: mit Userform suchen und filtern
25.05.2019 21:21:42
Matthias
Moin!
Der Suchcode kommt mir irgendwie bekannt vor. :-) Finde allerdings den Beitrag dazu nicht mehr. Deshalb fehlt mir grad das Hintergrundwissen ein wenig. Mal als Frage:
Du willst in die Textboxen Werte eingeben und die sollen mit den Spalten (1,3,4,5..) abgeglichen werden. Bei Übereinstimmung in der LIstbox dann anzeigen! Wenn ja, welche Textbox soll den für welche Spalte dienen? Derzeit sind die ja von der Nummerierung nicht identisch mit den Spalten in der Tabelle. Und soll da auch die Suche vom Zahlungseingang mit beinhaltet sein?
Wenn du da noch ein paar Angaben machen könntest, könnte man den Code umbauen bzw. was neues stricken.
VG
Anzeige
AW: mit Userform suchen und filtern
26.05.2019 09:42:09
Matthias
Moin!
Also hier mal eine Variante für deine Suche. Habe die dictionarys rausgenommen und es ein weniger anwenderfreundlich gestaltet. Damit kannst du den Code glaube ich besser anpassen und man weiß in 3 MOnaten noch, was warum geschieht. Beim Eintragen in der Listbox musst du mal schauen, da hattest du auch aus Spalte 12 was genommen. Deine Überschriften gingen aber nur bis Spalte 11. Habe das mal geändert, sollte aber überprüft werden. Um jetzt deine Textboxen auszuwerten, gibt es im Code 2 Arrays. Das erste ist boxenindex. Dort einfach die Indizes deiner auszuwertenden Boxen eintragen. Im Beispiel sind es die 3 linken (mit der Endnummer 2, 3 und 4). Darunter ist das Array spaltejebox. Da schreibst du zu der jeweiligen Box, in welcher Spalte der TAbelle der Werst steht. Die Reihenfolge sollte die gleich sein, wie bei dem Array boxenindex. Vorteil hierbei ist, du kannst das beliebig erweitern und brauchst die Boxen nicht in der Reihe der Spalten nummeriert haben (wäre sonst für eine Schleife notwendig). HAbe dazu noch nen paar Kommentare eingefügt. Einfach mal prüfen.
Private Sub CommandButton4_Click()
'suchen
Dim quelle As Object
Dim daten
Dim boxenindex(), spaltejebox()
Dim zeilen As Long, spalten As Long, zeile As Long, boxind As Long, spalte As Long
Dim einttragen As Boolean, suche As Boolean
Dim summe As Long
Dim wert, tbinhalt
Application.ScreenUpdating = False
Set quelle = Worksheets("Rechnungen")
Me.ListBox1.Clear
Me.ListBox1.ColumnCount = 8
'hier die Indizes der Boxen die ausgewählt werden sollen eintragen, darunter die Spalte je Box
boxenindex = Array(2, 3, 4)
spaltejebox = Array(10, 3, 11)
'prüfen ob die spalten für die Boxen ausreichen
If UBound(boxenindex)  "" Then
suche = True
wert = daten(zeile, spaltejebox(boxind))
'prüfen ob Wert vorkommt - UNterscheidung Groß und KLeinschreibung
If InStr(1, wert, tbinhalt, vbTextCompare) = 0 Then
'falls keine UNterscheidung der SChreibweise, dann die Zeile hier nehmen
'If InStr(1, UCase(wert), UCase(tbinhalt), vbTextCompare) = 0 Then
eintragen = False
Exit For
End If
End If
Next
If eintragen = True And suche = True Then
Me.ListBox1.AddItem
spalte = Me.ListBox1.ListCount - 1
Me.ListBox1.List(spalte, 0) = daten(zeile, 1)
Me.ListBox1.List(spalte, 1) = daten(zeile, 2)
Me.ListBox1.List(spalte, 2) = daten(zeile, 3)
Me.ListBox1.List(spalte, 3) = daten(zeile, 4)
Me.ListBox1.List(spalte, 4) = Format(CDbl(daten(zeile, 5)), "#,##0.00 €")
summe = summe + CDbl(daten(zeile, 5))
Me.ListBox1.List(spalte, 5) = daten(zeile, 9)
Me.ListBox1.List(spalte, 6) = daten(zeile, 10)
Me.ListBox1.List(spalte, 7) = daten(zeile, 11)
End If
Next
Me.TextBox_Betrag.Value = Format(CDbl(summe), "#,##0.00 €")
Application.ScreenUpdating = True
End Sub

VG
Anzeige
AW: mit Userform suchen und filtern
26.05.2019 09:49:03
Markus
Hallo Matthias,
ich war gerade am zusammenschreiben zu deiner vorherigen Antwort…
das kann schon sein, dass dir der Code bekannt vorkommt…
https://www.herber.de/cgi-bin/callthread.pl?index=1673864#1674449
War im Februar… Mir ist leider aufgefallen, dass ich mich dafür gar nicht bedankt habe ☹.
Der Code läuft für die damalige Anforderung super! Also an dieser Stelle noch mal vielen Dank!
Die neue Userform hat sich etwas vereinfacht…ich habe nocheinmal lange darüber nachgedacht was überhaupt benötigt wird…
Ich möchte folgende Suchanfragen abfragen und in der Listbox ausgeben:
1. Zeige mir zu Kunde x (Spalte 11) alle bezahlten Rechnungen (Spalte 9, Zahlungseingang) im gesamten Zeitraum der Liste (Textbox_Date1=leer, Textbox_Date2=leer, Cobobox=gefüllt)
2. Zeige mir zu Kunde x (Spalte 11) alle bezahlten Rechnungen (Spalte 9, Zahlungseingang) ab einem Zeitpunkt X (Textbox_Date1 =gefüllt; Textbox_Date2=leer; Combobox =gefüllt)
3. Zeige mir zu Kunde x (Spalte 11) alle bezahlten Rechnungen (Spalte 9, Zahlungseingang) bis einem Zeitpunkt X (Textbox_Date1=leer; Textbox_Date2 =gefüllt; Combobox =gefüllt)
4. Zeige mir zu Kunde x (Spalte 11) alle bezahlten Rechnungen (Spalte 9, Zahlungseingang) in einem Zeitraum (Textbox_Date1=gefüllt, Textbox_Date2 =gefüllt; Combobox =gefüllt)
5. Zeige für alle Kunden (Spalte 11) alle bezahlten Rechnungen (Spalte 9, Zahlungseingang) im gesamten Zeitraum der Liste (Textbox_Date1=leer, Textbox_Date2=leer, Cobobox=leer)
6. Zeige für alle Kunden (Spalte 11) alle bezahlten Rechnungen (Spalte 9, Zahlungseingang) ab einem Zeitpunkt X (Textbox_Date1 =gefüllt; Textbox_Date2=leer, Combobox =leer)
7. Zeige für alle Kunden (Spalte 11) alle bezahlten Rechnungen (Spalte 9, Zahlungseingang) bis einem Zeitpunkt X (Textbox_Date1=leer; Textbox_Date2 =gefüllt; Combobox =leer)
8. Zeige für alle Kunden (Spalte 11) alle bezahlten Rechnungen (Spalte 9, Zahlungseingang) in einem Zeitraum (Textbox_Date1=gefüllt, Textbox_Date2 =gefüllt; Combobox =leer)
In der Listbox sollen folgende zugehörigen Spalten der Filterung angezeigt werden:
Spalte 1, Spalte 2, Spalte 3; Spalte 4, Spalte 5, Spalte 9, Spalte 10, Spalte 11
Ich habe es gestern Abend noch über den umständlichen Weg der Filterung der Tabelle (neben der Zeit noch nach Kunden) hinbekommen, aber mit einem mehr oder weniger zufriedenstellenden Ergebnis…
Ziel wäre es wenn das ganze auch funktionieren würde ohne dass das Tabellenblatt gefiltert wird…
https://www.herber.de/bbs/user/130025.xlsm
Gruß Markus
Anzeige
AW: mit Userform suchen und filtern
26.05.2019 12:05:56
Markus
Vergessen das Häkchen zu setzen...
AW: mit Userform suchen und filtern
26.05.2019 12:20:50
Matthias
Moin!
Jetzt auf die schnelle vorm Mittagschlaf, probiere es mal so. Ist etwas umfangreicher, da ich vorher mal alle Varianten abprüfe (sonst müsste man das unten im Code für jede Zeile machen = ungünstig für die Laufzeit). Habe aber noch nicht alles getestet. Nimm ggf. mal die Events für die CB raus - das nervt sonst, wenn beim Eintragen erst gefiltert wird.
Private Sub CommandButton1_Click()
'filtern
Dim quelle As Object
Dim daten
Dim zeilen As Long, spalten As Long, zeile As Long, boxind As Long, spalte As Long
Dim eintragen As Boolean
Dim summe As Long
Dim wert, tbinhalt
Dim nameindex As Long, zeitindex As Long
Dim wert1, wert2, zeitstart, zeitende
Application.ScreenUpdating = False
Set quelle = Worksheets("Rechnungen")
Me.ListBox1.Clear
Me.ListBox1.ColumnCount = 8
zeilen = quelle.Cells(quelle.Rows.Count, 1).End(xlUp).Row
spalten = quelle.Cells(1, quelle.Columns.Count).End(xlToLeft).Column    'die Überschriften
daten = quelle.Cells(1, 1).Resize(zeilen, spalten)  'mit Überschriften in Zeile 1
'Namencontrols auswerten
'alle kunden, beide leer
nameindex = 1
'ausgewählter Kunde aus TB
If Me.TextBox1  "" Then
nameindex = 3
End If
'ausgewählter Kunde aus CB
If Me.ComboBox1.ListIndex  -1 Then
nameindex = 4
End If
'ausgewählter Kunde aus beiden Feldern
If Me.TextBox1  "" And Me.ComboBox1.ListIndex  -1 Then
nameindex = 2
End If
'Zeit auswerten
zeitindex = 1
'von
If Me.TextBox_Date1  "" Then
zeitindex = 2
zeitstart = CDbl(CDate(Me.TextBox_Date1))
End If
'bis
If Me.TextBox_Date2  "" Then
zeitindex = 3
zeitende = CDbl(CDate(Me.TextBox_Date2))
End If
'von bis
If Me.TextBox_Date1  "" And Me.TextBox_Date2  "" Then
zeitindex = 4
zeitstart = CDbl(CDate(Me.TextBox_Date1))
zeitende = CDbl(CDate(Me.TextBox_Date2))
End If
For zeile = 2 To zeilen '2 da in der ersten Zeile Überschriften stehen
eintragen = True
wert1 = daten(zeile, 10)    'kd nr
wert2 = daten(zeile, 11)    'kd na
Select Case nameindex
Case 1  'alle Namen
If wert1 = "" And wert2 = "" Then eintragen = False
Case 2  'gew Name beide
If InStr(1, wert1, Me.TextBox1, vbTextCompare) = 0 Or _
InStr(1, wert2, Me.ComboBox1, vbTextCompare) = 0 Then eintragen = False
Case 3  'gew Name tb
If InStr(1, wert1, Me.TextBox1, vbTextCompare) = 0 Then eintragen = False
Case 4  'gew Name cb
If InStr(1, wert2, Me.ComboBox1, vbTextCompare) = 0 Then eintragen = False
End Select
If eintragen = True Then
Select Case zeitindex
Case 1 'alle
If daten(zeile, 9) = "" Then eintragen = False
Case 2  'von
If CDbl(daten(zeile, 9))  zeitende Then  _
eintragen = False
End Select
End If
If eintragen = True Then
Me.ListBox1.AddItem
spalte = Me.ListBox1.ListCount - 1
Me.ListBox1.List(spalte, 0) = daten(zeile, 1)
Me.ListBox1.List(spalte, 1) = daten(zeile, 2)
Me.ListBox1.List(spalte, 2) = daten(zeile, 3)
Me.ListBox1.List(spalte, 3) = daten(zeile, 4)
Me.ListBox1.List(spalte, 4) = Format(CDbl(daten(zeile, 5)), "#,##0.00 €")
summe = summe + CDbl(daten(zeile, 5))
Me.ListBox1.List(spalte, 5) = daten(zeile, 9)
Me.ListBox1.List(spalte, 6) = daten(zeile, 10)
Me.ListBox1.List(spalte, 7) = daten(zeile, 11)
End If
Next
Me.TextBox_Betrag.Value = Format(CDbl(summe), "#,##0.00 €")
Application.ScreenUpdating = True
End Sub

VG
Anzeige
AW: mit Userform suchen und filtern
26.05.2019 13:02:37
Markus
Hallo Matthias,
das sieht super aus. Vielen Dank.
Die Events in der CB habe ich rausgenommen... waren ja auch nur mein "Weg". Geht jetzt alles super schnell.
Einzig ist wenn ich z.B. alle Daten bis 01.01.18 (Textbox_Date2=gefüllte, TextBox_Date1=leer) suchen möchte gibt die Liste alle Werte ab 01.01.18 aus... den Fehler habe ich aber gefunden; ein ">" statt ein " Vielen Dank
Gruß Markus
AW: mit Userform suchen und filtern
26.05.2019 14:12:36
Markus
Ich kann den Fehler noch einschränken:
Es passiert nur, wenn nach allen Rechnungen "bis" gesucht wird...
AW: mit Userform suchen und filtern
26.05.2019 15:35:18
Matthias
Moin!
Da bin ich wieder. Also das mit dem > hast du schon gefunden. Die anderen Vergleiche sollten passen. Kam vorhin nicht zum Testen und der Fehler war beim C&P passiert. Bzgl. der Daten ohne Zahlungseingang hier gändert. Habe nur eine Zeile ergänzt. Jetzt sollte es passen. Die Suche läuft über die TB1 und die CB1 für den NAmen. Wenn du da nur die CB nutzt, kann man den Code auch kürzen. War mir da aber nicht sicher.
Private Sub CommandButton1_Click()
'filtern
Dim quelle As Object
Dim daten
Dim zeilen As Long, spalten As Long, zeile As Long, boxind As Long, spalte As Long
Dim eintragen As Boolean
Dim summe As Long
Dim wert, tbinhalt
Dim nameindex As Long, zeitindex As Long
Dim wert1, wert2, zeitstart, zeitende
Application.ScreenUpdating = False
Set quelle = Worksheets("Rechnungen")
Me.ListBox1.Clear
Me.ListBox1.ColumnCount = 8
zeilen = quelle.Cells(quelle.Rows.Count, 1).End(xlUp).Row
spalten = quelle.Cells(1, quelle.Columns.Count).End(xlToLeft).Column    'die Überschriften
daten = quelle.Cells(1, 1).Resize(zeilen, spalten)  'mit Überschriften in Zeile 1
'Namencontrols auswerten
'alle kunden, beide leer
nameindex = 1
'ausgewählter Kunde aus TB
If Me.TextBox1  "" Then
nameindex = 3
End If
'ausgewählter Kunde aus CB
If Me.ComboBox1.ListIndex  -1 Then
nameindex = 4
End If
'ausgewählter Kunde aus beiden Feldern
If Me.TextBox1  "" And Me.ComboBox1.ListIndex  -1 Then
nameindex = 2
End If
'Zeit auswerten
zeitindex = 1
'von
If Me.TextBox_Date1  "" Then
zeitindex = 2
zeitstart = CDbl(CDate(Me.TextBox_Date1))
End If
'bis
If Me.TextBox_Date2  "" Then
zeitindex = 3
zeitende = CDbl(CDate(Me.TextBox_Date2))
End If
'von bis
If Me.TextBox_Date1  "" And Me.TextBox_Date2  "" Then
zeitindex = 4
zeitstart = CDbl(CDate(Me.TextBox_Date1))
zeitende = CDbl(CDate(Me.TextBox_Date2))
End If
For zeile = 2 To zeilen '2 da in der ersten Zeile Überschriften stehen
eintragen = True
wert1 = daten(zeile, 10)    'kd nr
wert2 = daten(zeile, 11)    'kd na
Select Case nameindex
Case 1  'alle Namen
If wert1 = "" And wert2 = "" Then eintragen = False
Case 2  'gew Name beide
If InStr(1, wert1, Me.TextBox1, vbTextCompare) = 0 Or _
InStr(1, wert2, Me.ComboBox1, vbTextCompare) = 0 Then eintragen = False
Case 3  'gew Name tb
If InStr(1, wert1, Me.TextBox1, vbTextCompare) = 0 Then eintragen = False
Case 4  'gew Name cb
If InStr(1, wert2, Me.ComboBox1, vbTextCompare) = 0 Then eintragen = False
End Select
If daten(zeile, 9) = "" Then eintragen = False
If eintragen = True Then
Select Case zeitindex
Case 1 'alle
If daten(zeile, 9) = "" Then eintragen = False
Case 2  'von
If CDbl(daten(zeile, 9))  zeitende Then eintragen = False
Case 4 ' beide
If CDbl(daten(zeile, 9))  zeitende Then  _
eintragen = False
End Select
End If
If eintragen = True Then
Me.ListBox1.AddItem
spalte = Me.ListBox1.ListCount - 1
Me.ListBox1.List(spalte, 0) = daten(zeile, 1)
Me.ListBox1.List(spalte, 1) = daten(zeile, 2)
Me.ListBox1.List(spalte, 2) = daten(zeile, 3)
Me.ListBox1.List(spalte, 3) = daten(zeile, 4)
Me.ListBox1.List(spalte, 4) = Format(CDbl(daten(zeile, 5)), "#,##0.00 €")
summe = summe + CDbl(daten(zeile, 5))
Me.ListBox1.List(spalte, 5) = daten(zeile, 9)
Me.ListBox1.List(spalte, 6) = daten(zeile, 10)
Me.ListBox1.List(spalte, 7) = daten(zeile, 11)
End If
Next
Me.TextBox_Betrag.Value = Format(CDbl(summe), "#,##0.00 €")
Application.ScreenUpdating = True
End Sub

VG
Anzeige
AW: mit Userform suchen und filtern
26.05.2019 18:52:59
Markus
Hallo Matthias!
Super! Vielen, vielen Dank! Läuft genau so wie ich es mir vorgestellt habe!
Gruß
Markus
AW: mit Userform suchen und filtern
26.05.2019 14:08:58
Markus
Hallo Matthias,
eines ist mir jetzt noch aufgefallen bei einfügen in meine "richtige" Datei...
Wenn es Rechnungen gibt, die noch nicht bezahlt sind, sprich in Spalte 9 noch kein Datum drin steht, so werden diese trotzdem mit gefiltert und angezeigt (z.B. habe ich nach Rechnungen in 2017 gefiltert, eine unbezahlte von 2019 wurde aber mit angezeigt...) Dass sollte jedoch nicht der Fall sein. Es sollen nur alle Zeilen angezeigt werden, die in Spalte 9 (Zahlungseingang) ein Datum steht.
Gruß Markus
Anzeige
AW: mit Userform suchen und filtern
25.05.2019 09:12:54
Hajo_Zi
Hallo Markus,
der Fehler von Matthias und weitere Fehler wären aufgefallen, wenn man in der ersten Zeile
Option Explicit
geschrieben hätte. Aber das machen wahrscheinlich nur Programmierer.

Beiträge von Werner, Luc, robert, J.O.Maximo und folgende lese ich nicht.
Die Beiträge werden auch ignoriert, es erfolgt keine Antwort.
AW: mit Userform suchen und filtern
25.05.2019 09:22:36
Luschi
Hallo Markus,
hier mal ein Anfang von mir, betrifft den Datumsfilter.
https://www.herber.de/bbs/user/130014.xlsm
Gruß von Luschi
aus klein-Paris
Anzeige
AW: mit Userform suchen und filtern
25.05.2019 11:18:44
Markus
Hallo Luschi,
danke für deine Hinweise und die Bearbeitung des Codes. Hilft mir auf jeden Fall schon mal weiter!
Gruß Markus

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige