Suchmaschine

Live-Suche in der Excel-Suchmaschine

Roman VBA (Makros) , , , , , , , , , , ,

Wie Du in Excel eine simple Suchmaschine programmieren und einrichten kannst, wurde in einem älteren Beitrag (siehe Suchmaschine programmieren) illustriert. Hier wird nun ergänzt, wie Du eine Live-Suche etablierst, damit direkt nach Eingabe eines Suchkriteriums die Liste auf die entsprechenden Einträge gefiltert wird.

Nehmen wir nach wie vor an, dass in der Zelle "B1" das Suchkriterium steht. Falls sich also diese Zelle ändert, hat die Suchmaschine direkt zu reagieren. Das Makro aus dem Beitrag "Suchmaschine programmieren" ist hierzu nicht in einem Modul abzulegen, sondern direkt im entsprechenden Tabellenblatt (=Tabelle1 (Suchmaschine))!

vbaprojekttabelle

Zudem ist der Code leicht anzupassen, respektive  um nachfolgende zusätzliche Angaben zu ergänzen. Der vollständige Code wird dann weiter unten aufgeführt.

Anstelle von "Sub Suchmaschine()" zu Beginn hat die erste Codezeile zu lauten:

Private Sub worksheet_change(ByVal target As Range)

Zudem ist eine Wenn-Dann-Kondition einzubauen, die auf die Zelle "B1" verweist - und eben beim Anpassen dieser Zelle das Makro automatisch auslöst.

If target.Row = 1 And target.Column = 2 Then

Darauf folgt der gesamte Code der Suchmaschine, abgeschlossen (vor "End Sub") mit dem Befehl:

End If

Der vollständige Code ...

... (nicht in Modul1 sondern im Tabellenblatt direkt, wie oben erwähnt) lautet demnach:

Private Sub worksheet_change(ByVal target As Range)

If target.Row = 1 And target.Column = 2 Then

Range("D2").Select
Selection.AutoFilter
ActiveSheet.Range("$A$2:$D$8").AutoFilter Field:=4, Criteria1:="<>0"

AnzahlTreffer = Application.WorksheetFunction.Subtotal(3, Range("A:A")) - 2

MsgBox ("Insgesamt " & AnzahlTreffer & " Treffer gefunden.")

End If

End Sub

Die Eingabe des Platzhalters/Sternchens (*) erübrigen

Beim Programmieren der Suchmaschine habe ich Dir aufgezeigt, dass das Suchkriterium jeweils mit einem Sternchen vor- und nachher versehen werden sollte - beispielsweise "*Pop*". Um diesem vorzubeugen (es ist also nur noch der Begriff einzugeben, ohne Platzhalter-Symbol), ist die WVERWEIS-Formel minim anzupassen. Wir kombinieren jeweils den Text "*" mit dem Zellbezug auf das Suchkriterium in "B1". Für die Zeile 3 lautet die Formel wie folgt (und kann auf sämtliche weiteren Zeilen herunterkopiert werden):

=WENNFEHLER(WVERWEIS("*"&$B$1&"*";A3:C3;1;FALSCH);0)

Suchmaschine programmieren

Roman Formeln, VBA (Makros) , , , , , , ,

Sobald eine Excel-Tabelle viele Einträge hat, sind Filter beinahe unumgänglich. Man kann einzelne Begriffe selbstverständlich rasch mittels der Such-Funktion "CTRL + F" finden. Wir wollen hier jedoch in VBA unsere eigene Suchmaschine programmieren, um wiederkehrend unsere Datenbank nach einem gewünschten Suchkriterium zu durchsuchen. Unser fertiges Produkt soll somit nur diejenigen Zeilen anzeigen, welche auch dieses Kriterium enthalten. In diesem Beitrag programmieren wir eine solche Suchmaschine für folgende Musik-Datenbank:

Datenbank

Vorbereitungsschritte

Wir fügen nun oberhalb der Überschrift eine neue Zeile 1 ein. In "A1" schreiben wir "Suchkriterium:" und die Zelle "B1" markieren wir farbig - dort soll jeweils unser Suchbegriff eingegeben werden. In "C1" fügen wir ein Textfeld ein und schreiben "Suche starten!" - dies wird unser Button, um das Makro später auszulösen. Damit unser Filter auf das Suchkriterium abgestimmt werden kann, müssen wir in Spalte D die nachfolgende Formel einsetzen. Für die Zeile Nummer 3 (Zelle "D3") lautet diese für's Erste:

=WVERWEIS($B$1;A3:C3;1;FALSCH)

Du kannst nun einmal einen Suchbegriff eingeben und das Resultat für die Formel "WVERWEIS(...)" betrachten:

WVERWEIS

Ich empfehle Dir, die Suchbegriffe jeweils mit Sternchen (*) einzugeben, damit Du sicher alle Resultate angezeigt bekommst, welche den Begriff enthalten.

Die Formel "WVERWEIS(...)" müssen wir noch leicht anpassen, um den Filter besser anwenden zu können. Wir wenden hierzu zusätzlich die Formel "WENNFEHLER" und schreiben:

=WENNFEHLER(WVERWEIS($B$1;A3:C3;1;FALSCH);0)

Du siehst nun: Überall, wo vorhin "#NV" war, steht nun "0". Die Fehlermeldung "#NV" hatte uns lediglich angezeigt, dass in der entsprechenden Zeile nichts dem Suchkriterium entspricht. Die "0" wird für uns im Makro jedoch viel einfacher auszugrenzen sein.

Automatisch filtern mit VBA

Wir setzen nun den Filter auf Zeile zwei (unsere Überschrift) und beginnen, ein Makro aufzuzeichnen. Sobald der Rekorder gestartet ist, filtern wir sämtliche "0" heraus und stoppen die Aufnahme bereits wieder. Folgender Code wurde in der Zwischenzeit aufgezeichnet:

ActiveSheet.Range("$A$2:$D$8").AutoFilter Field:=4, Criteria1:="Pop"

Allerdings entspricht dies nicht ganz dem, was wir eigentlich wollten. Die Intention war es ja, alle "0" auszufiltern. Deshalb müssen wir den letzten Teil des Filterkriteriums ("Criteria1:="Pop") anpassen zu:

Criteria1:="<>0"

Wir können nun ein anderes Suchkriterium eingeben und unser Makro ausführen - es funktioniert wie gewünscht! Zuletzt können wir das Makro unserem Textfeld ("Suche starten!") zuweisen. Mittels Rechtsklick auf das Textfeld → "Makro zuweisen" kannst Du dein zuvor aufgezeichnetes Makro selektieren.

"Message-Box" über die Anzahl Treffer

Nehmen wir uns noch zwei ästhetischen Punkten an: Wir können die Spalte D problemlos ausblenden, das Makro wird nach wie vor funktionieren. Desweiteren möchten wir eine "Message-Box" einrichten, die uns jeweils die Anzahl der gefundenen Treffer angibt. Wir geben eine weitere Codezeile ein:

AnzahlTreffer = Application.WorksheetFunction.Subtotal(3, Range("A:A"))-2

Die Variable "AnzahlTreffer" umfasst das Resultat der Funktion "TEILERGEBNIS" unter der Verwendung von "ANZAHL2". Wir subtrahieren "2", um die beschriebenen Felder "A1" und "A2" abzuziehen. Nun können wir die nächste Codezeile einfügen:

MsgBox ("Insgesamt " & AnzahlTreffer & " Treffer gefunden.")

 ... und dies ist unser Resultat:

 MsgBox