Office: Text in Tabelle suchen und in Listbox ausgeben

Toulousi · 10. Juli 2016

Hallo

ich bin ein VBA Anfänger und habe folgendes Problem, aber ich komme nicht weiter.

Ich habe eine Tabelle mit Adressen in der die Spalten
A (Kundennummer)
B (Name)
C (Straße)
D (PLZ)
E (Wohnort)
beschriftet sind mit Daten. Nun habe ich ein UserForm erstellt mit einer Textbox (txtsuche), einem Suche-Button und einer Listbox.

Es soll, wenn ich auf den Suchen-Button klicke, nach dem "Wort" in der Textbox gesucht werden. In dem Bereich B - E der Tabelle.

Alle Treffer sollen in der Listbox aufgeführt werden. Allerdings nicht nur einer Sondern alle die in der Tabelle stehen.

Bsp.

Ich suche nach dem Namen "Müller"

Wurde der Name Müller gefunden, so soll in der Listbox aufgeführt werden:

10215 - Müller - Seeweg 5 - 10000 Berlin

Anschließend sollen alle anderen gesucht werden die Müller heißen oder Straßen die Müller in Namen haben und ebenfalls so aufgelistet werden.

Hab schon viele Beispiele für Suche gesehen, allerdings steige ich nicht durch den Code. *sos

Danke schon mal für eure Hilfe.

:)

Hajo_Zi · 12. Juli 2016

Ich baue keine Datei nach. Die Zeit hat schon jemand investiert.
Ein Nachbau sieht meist anders aus als das Original.
Der Name einer hochgeladenen Mappe wird im Beitrag automatisch angezeigt, sodass es bei Verwendung von aussagekräftigen Namen leichter fällt, sie später im Ablageordner wiederzufinden und sie gedanklich einem bestimmten Thema zuzuordnen. Namen wie Test, Mappe, Beispiel usw. sind so allgemein, dass eine Zuordnung zu einem Thema unmöglich gemacht wird.

Beverly · 12. Juli 2016

Hi,

benutze die Range.FindNext-Methode: https://msdn.microsoft.com/de-de/lib.../ff196143.aspx

Toulousi · 12. Juli 2016

Sorry,

ich steig nicht durch... sitze seit ein paar Stunden da und bin noch keinen Schritt weiter. Ich habe die Datei angehängt und hoffe das mir jemand helfen kann. *stupid

Danke

Fennek11 · 12. Juli 2016

Hallo,

falls eine Alternative ohne vba gesucht wird, mit der Arbeitsblatt-Funktion "Aggragat" kann die Aufgabe auch gelöst werden.

mfg

Beverly · 12. Juli 2016

Hi,

deine Beschreibung im Eröffnungsbeitrag stimmt nicht mit deiner hochgeladenen Mappe überein. Mein Code bezieht sich auf die hochgeladene Mappe.

Code:

Private Sub cmdOK_Click()
Dim rngZelle As Range
Dim strStart As String
Dim arrZeile()
Dim lngZeile As Long
Dim lngZaehler As Long
Dim varVorhanden
ReDim arrZeile(0)
Set rngZelle = Columns("C:F").Find(Me.txtSuchen, lookat:=xlPart)
If Not rngZelle Is Nothing Then
strStart = rngZelle.Address
Do
' prüfen ob Zeilennummer bereits vorhanden
varVorhanden = Application.Match(rngZelle.Row, arrZeile(), 0)
' Zeilennummer noch nicht vorhanden
If Not IsNumeric(varVorhanden) Then
' Array redimensionieren
ReDim Preserve arrZeile(0 To lngZaehler)
' Zeile ins Array schreiben
arrZeile(lngZaehler) = rngZelle.Row
' neuen Eintrag in ListBox
Me.ListBox1.AddItem Cells(rngZelle.Row, 1)
ListBox1.List(ListBox1.ListCount - 1, 1) = Cells(rngZelle.Row, 3)
ListBox1.List(ListBox1.ListCount - 1, 2) = Cells(rngZelle.Row, 4)
ListBox1.List(ListBox1.ListCount - 1, 3) = Cells(rngZelle.Row, 5)
ListBox1.List(ListBox1.ListCount - 1, 5) = Cells(rngZelle.Row, 6)
ListBox1.List(ListBox1.ListCount - 1, 6) = Cells(rngZelle.Row, 7)
' in letzte ListBox-Spalte Zeilennummer, damit der Datensatz später wiedergefunden wird
ListBox1.List(ListBox1.ListCount - 1, 7) = rngZelle.Row
lngZaehler = lngZaehler + 1
End If
' nächsten Treffer suchen
Set rngZelle = Columns("C:F").FindNext(rngZelle)
Loop While rngZelle.Address strStart
End If
Set rngZelle = Nothing
End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Beachte, dass du unter der Eigenschaft ColumnCount der ListBox 8 eintragen musst, damit alle Spalten vorhanden sind. Die Spaltenbreite kannst du unter der Eigenschaft ColumnWidths individuell anpassen, wobei du für die letzte Spalte 0 angeben kannst, damit die Zeilennummer nicht mit dargestellt wird - z.B. so: 80 Pt;100 Pt;100 Pt;100 Pt;100 Pt;100 Pt;100 Pt;0 Pt

Toulousi · 13. Juli 2016

Sehr super,

vielen, vielen Dank Beverly.

Allerdings habe ich ein Problem ausgemacht. Es funkioniert nur wenn ich auf diesem Tabellenblatt bin. Öffne ich die Form wenn ich auf einem anderen Tabellenblatt bin dann funktioniert es nicht.

Was muss ich denn ändern, damit es immer auf den Bereich des Tabellenblattes "Adressen" zugreift?

Die Datenbank ist nur ein Teil eines größeren Projektes und ich will nicht immer zwischen den Tabellenblättern hin und her springen.

P. S. Sorry das meine erste Beschreibung falsch war. Bin noch nicht so sicher im Umgang mit Foren-Beiträgen und wie ich es am besten beschreibe.

Toulousi · 12. Januar 2021

von Fennek11

Hallo,

falls eine Alternative ohne vba gesucht wird, mit der Arbeitsblatt-Funktion "Aggragat" kann die Aufgabe auch gelöst werden.

mfg
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Hallo,

leider reicht die Aggragat-Funktion nicht aus. Trotzdem Danke.

Mfg

Tacker · 13. Juli 2016

Hallo Toulousi,

eine Möglichkeit wäre das Tabellenblatt "Adressen" als aktiv zu setzen

Code:

Worksheets("Adressen").Activate
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

und den dann vor den Berechnungen einfügen

oder du verweist bei jedem Zugriff auf das Adressen Tabellenblatt (würde ich nicht unbedingt empfehlen ist unnötig) sähe dann beispielsweise so aus

Code:

ListBox1.List(ListBox1.ListCount - 1, 1) = Worksheets("Adressen").Cells(rngZelle.Row, 3)
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

oder du setzt eine with anweisung

sähe dann im Beispiel so aus

Code:

With Worksheets("Adressen")

ListBox1.List(ListBox1.ListCount - 1, 1) = .Cells(rngZelle.Row, 3)
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

End With
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Gruß Tacker

Beverly · 13. Juli 2016

Hi,

ich war von der hochgeladenen Mappe ausgegangen. Hier der komplette angepasste Code:

Code:

Private Sub cmdOK_Click()
Dim rngZelle As Range
Dim strStart As String
Dim arrZeile()
Dim lngZeile As Long
Dim lngZaehler As Long
Dim varVorhanden
ReDim arrZeile(0)
With Worksheets("Adressen")
Set rngZelle = .Columns("C:F").Find(Me.txtSuchen, lookat:=xlPart)
If Not rngZelle Is Nothing Then
strStart = rngZelle.Address
Do
' prüfen ob Zeilennummer bereits vorhanden
varVorhanden = Application.Match(rngZelle.Row, arrZeile(), 0)
' Zeilennummer noch nicht vorhanden
If Not IsNumeric(varVorhanden) Then
' Array redimensionieren
ReDim Preserve arrZeile(0 To lngZaehler)
' Zeile ins Array schreiben
arrZeile(lngZaehler) = rngZelle.Row
' neuen Eintrag in ListBox
Me.ListBox1.AddItem .Cells(rngZelle.Row, 1)
ListBox1.List(ListBox1.ListCount - 1, 1) = .Cells(rngZelle.Row, 3)
ListBox1.List(ListBox1.ListCount - 1, 2) = .Cells(rngZelle.Row, 4)
ListBox1.List(ListBox1.ListCount - 1, 3) = .Cells(rngZelle.Row, 5)
ListBox1.List(ListBox1.ListCount - 1, 5) = .Cells(rngZelle.Row, 6)
ListBox1.List(ListBox1.ListCount - 1, 6) = .Cells(rngZelle.Row, 7)
' in letzte ListBox-Spalte Zeilennummer, damit der Datensatz später wiedergefunden wird
ListBox1.List(ListBox1.ListCount - 1, 7) = rngZelle.Row
lngZaehler = lngZaehler + 1
End If
' nächsten Treffer suchen
Set rngZelle = .Columns("C:F").FindNext(rngZelle)
Loop While rngZelle.Address strStart
End If
End With
Set rngZelle = Nothing
End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Beachte den . (Punkt) vor Cells und Columns - damit wird der Bezug zum Tabellenblatt festgelegt, welches mit der Zeile With Worksheets("Adressen") angegeben ist.

Toulousi · 13. Juli 2016

Hey,
hab alle drei Varianten ausprobiert. Die erste Variante funzt ruft mir die Arbeitsmappe jedoch auf, und das möchte ich nicht.

Variante 2 und 3 funzen gar nicht. Mein Code sieht nun so aus, was mache ich falsch?

Code:

Private Sub cmdOK_Click()

Dim rngZelle As Range
Dim strStart As String
Dim arrZeile()
Dim lngZeile As Long
Dim lngZaehler As Long
Dim varVorhanden
ReDim arrZeile(0)
Set rngZelle = Columns("B:F").Find(Me.txtSuchen, lookat:=xlPart)

If Not rngZelle Is Nothing Then
strStart = rngZelle.Address
Do
' prüfen ob Zeilennummer bereits vorhanden
varVorhanden = Application.Match(rngZelle.Row, arrZeile(), 0)
' Zeilennummer noch nicht vorhanden
If Not IsNumeric(varVorhanden) Then
' Array redimensionieren
ReDim Preserve arrZeile(0 To lngZaehler)
' Zeile ins Array schreiben
arrZeile(lngZaehler) = rngZelle.Row
' neuen Eintrag in ListBox

Me.ListBox1.AddItem Cells(rngZelle.Row, 1)
With Worksheets("Adressen")
ListBox1.List(ListBox1.ListCount - 1, 1) = .Cells(rngZelle.Row, 3)
ListBox1.List(ListBox1.ListCount - 1, 2) = .Cells(rngZelle.Row, 4)
ListBox1.List(ListBox1.ListCount - 1, 3) = .Cells(rngZelle.Row, 5)
ListBox1.List(ListBox1.ListCount - 1, 5) = .Cells(rngZelle.Row, 6)
ListBox1.List(ListBox1.ListCount - 1, 6) = .Cells(rngZelle.Row, 7)
End With
' in letzte ListBox-Spalte Zeilennummer, damit der Datensatz später wiedergefunden wird
ListBox1.List(ListBox1.ListCount - 1, 7) = rngZelle.Row
lngZaehler = lngZaehler + 1

End If
' nächsten Treffer suchen
Set rngZelle = Columns("B:F").FindNext(rngZelle)
Loop While rngZelle.Address strStart
End If
Set rngZelle = Nothing

End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Toulousi · 13. Juli 2016

Danke für den angepassten Code. Funktioniert bestens.

Tacker · 13. Juli 2016

Hallo Toulousi,

siehe dem Code von Beverly der sollte funktionieren,

der erste Fehler den ich gesehen habe bei dir war die Zeile

Set rngZelle = Columns("B:F").Find(Me.txtSuchen, lookat:=xlPart)

dort hätte man bereits auf die Tabelle "Adressen" verweisen müssen, also auf deutsch: die With-Anweisung in deinem Code kommt zu spät, Beverly macht es richtig.

den Rest hab ich mir nicht mehr angeschaut.

Gruß Tacker

xlph · 13. Juli 2016

......oder so.......

Office: Text in Tabelle suchen und in Listbox ausgeben

Text in Tabelle suchen und in Listbox ausgeben

Text in Tabelle suchen und in Listbox ausgeben

Text in Tabelle suchen und in Listbox ausgeben

Text in Tabelle suchen und in Listbox ausgeben

Text in Tabelle suchen und in Listbox ausgeben

Text in Tabelle suchen und in Listbox ausgeben - Similar Threads - Text Tabelle Listbox

Eingefügtes Bild in einer Tabelle verschiebt den Text

Text auflisten wenn Wert in gleicher Spalte enthält X

iPad Tabelle verschiebt Text wenn zwei Minusse eingeben werden

Word Tabelle Text in Spalte vertikal statt horizontal

Anzahl aus Text auslesen

Tabelle mit Kostenstellen soll in einer Zeile die entsprechende Niederlassung als Text ausgeben

Text in Word Tabelle abhängig von Nachbarzelle