Xtract: Auf der Basis VBA/Makro wird in Excel eine definierte Spalte nach einem Begriff durchsucht und die ganze Zeile in ein anderes Arbeitsblatt kopiert. Dabei werden auch „Joker“ als Platzhalter für eine definierte Anzahl oder beliebig viele Zeichen verwendet.
Daten in einer Spalte suchen und Zeile kopieren
Mit schöner Regelmäßigkeit kommt in Foren die Anfrage nach diesem Problem: Es soll in einer Spalte ein bestimmter Wert gefunden und bei Erfolg die Daten der Zeile in ein anderes Blatt kopiert werden. Der Lösungsmöglichkeiten gibt es diverse, teils einfache, teils anspruchsvolle. Einige davon werden wir Ihnen hier vorstellen. Mitunter mit nur einigen Hinweisen, öfter mit VBA-Code.
Die Datenbasis
Gewiss wollen Sie das, was hier beschrieben wird, auch nachvollziehen können. Dazu ist es ausgesprochen hilfreich, wenn Sie mit den gleichen Daten arbeiten wie wir. Darum laden Sie gleich diese Arbeitsmappe herunter. Es ist eine Liste der Abgeordneten des Deutschen Bundestages, Stand Mitte 2015. Etwas Arbeit sollten Sie jedoch im Vorwege investieren:
- Benennen Sie das Tabellenblatt um in Suchen&Kopieren
- Wenn Sie es vorziehen, können Sie auch eine neue erste Zeile einfügen und mit Überschriften versehen
- Und bei der Gelegenheit fügen Sie auch gleich ein neues, leeres Tabellenblatt hinzu, welches den Namen Ziel bekommen sollte.
Ohne Makro/VBA
Die einfachste Lösung, welche wir Ihnen hier vorstellen ist in erster Linie ein ganz normaler Filter. Sie lassen sich per AutoFilter oder in einer Intelligenten Tabelle nur den oder die Namen anzeigen, welche relevant sind. Diese ganzen Zeilen oder nur die Bereiche mit Daten markieren, StrgC zum kopieren; dann wechseln Sie in die Ziel – Tabelle und fügen beispielsweise per StrgV den Inhalt der Zwischenablage an der gewünschten Position ein. Anschließend schalten Sie den Filter wieder aus und Sie können eine neue Suche beginnen.
Wenn Sie diesen Vorgang öfter einmal durchführen müssen, dann ist vielleicht auch der Spezialfilter für Sie von Interesse. Sie finden diesen im Menü Daten | Sortieren und Filtern | Erweitert. Er mag anfangs etwas ungewohnt sein, aber nach einer kurzen Eingewöhnung ist das Arbeiten damit recht komfortabel.
So richtig empfehlen mögen wir es nicht, aber es geht natürlich auch über die Suchen-Funktionalität des Excel. Das ist aber recht umständlich, insbesondere dann, wenn der zu findende Term mehrfach in der Auflistung vorhanden ist.
Mit Makro / VBA
Im Prinzip (und aus unserer Sicht sowieso) ist dieses die elegantere Lösung des Problems. Sie können rasch einen Suchbegriff eingeben und „wie von Geisterhand“ erledigt Excel den Rest für Sie. Die einzelne oder auch mehrere Fundstellen werden automatisch in das Ziel – Blatt kopiert, dort natürlich automatisch an das Ende der Liste angefügt. Und da ist mit relativ wenig Aufwand auch einiges an Variationen machbar.
In diesem ersten Beitrag beschränken wir uns auf eine sehr simple Form des kopierens. Kleine Variationen sind hier angesprochen, unterschiedliche und aufwendigere Vorgänge werden in Folgebeiträgen besprochen. Die Datenbasis ist -zumindest jetzt noch- die weiter oben angesprochene Abgeordneten-Liste. Sie eignet sich sehr schön, weil über 600 Datensätze schon mehr als nur „Spielkram“ sind. 😉
Hinweis: Grundsätzlich stellen wir Ihnen zu diesem Thema keine fertigen *.xlsm-Dateien zur Verfügung. Sie finden den Quellcode in einigen Fällen direkt hier im Beitrag geschrieben, in jedem Fall aber als gepackte Datei zum Download. Wie Sie den Code in eine Excel-Mappe einbinden können, das können Sie hier nachlesen. – Wenn nichts anderes vermerkt ist, können Sie den Code in das Modul des Arbeitsblatts, wo die Quell-Daten drin stehen (hier: Suchen&Kopieren) oder in das Modul DieseArbeitsmappe einfügen. Das gilt aber nur für dieses Thema, keineswegs für andere Beiträge dieses Blogs.
Die „Grundausstattung“ 😉
Hier nun der Beispiel-Code, den Sie an dieser Stelle als gepackte Textdatei herunter laden können:
Option Explicit Sub FindAndCopy1() Dim rngSuch As Range, wksSrc As Worksheet, wksDst As Worksheet Dim strSuch As String, rngFound As Range Dim strFirst As String, FoundAdr As String Dim ZeSrc As Integer, ZeDst As Integer, lRow As Long, lRowDst As Long Set wksSrc = Tabelle1 'Codename! Set wksDst = Tabelle2 'Nicht der Blattname (der leicht umbenannt werden kann) lRow = wksSrc.Cells(Rows.Count, 1).End(xlUp).Row Set rngSuch = wksSrc.Range("A2:B" & lRow) 'Name oder Vorname suchen With wksDst lRowDst = WorksheetFunction.Max(2, .Cells(Rows.Count, 1).End(xlUp).Row) wksDst.Range("A2:B" & lRowDst).EntireRow.Delete 'Alle Namen-Einträge löschen If .Range("A1") = "" Then 'Wichtig, damit in A1 etwas steht (eventuell anpassen) .Cells(1, 1) = "Name" .Cells(1, 2) = "Vorname" .Cells(1, 3) = "Fraktion" End If End With strSuch = InputBox("Bitte das Suchwort eingeben", "Filter") With rngSuch Set rngFound = .Find(what:=strSuch) If Not rngFound Is Nothing Then strFirst = rngFound.Address Do FoundAdr = rngFound.Address ZeSrc = rngFound.Row ZeDst = wksDst.Cells(Rows.Count, 1).End(xlUp).Row + 1 wksSrc.Range("A" & ZeSrc & ":B" & ZeSrc).Copy wksDst.Cells(ZeDst, 1) 'Zeile von Spalte A bis B kopieren Set rngFound = .FindNext(rngFound) Loop While Not rngFound Is Nothing And rngFound.Address <> strFirst Else MsgBox "Der Name '" & strSuch & "' wurde nicht gefunden!", vbInformation, "Fehleingebe?" End If End With End Sub
Der Code ist recht einfach gehalten, darum auch ohne Kommentare. Probieren Sie ihn einfach einmal aus; beachten Sie aber, dass hier in jedem Fall das Arbeitsblatt Ziel existieren muss und auch der Name des Sheets mit den Quelldaten muss Suchen&Kopieren sein, wenn Sie den Code nicht entsprechend angepasst haben.
Genauigkeit ist Trumpf
Spätestens jetzt sollten Sie einmal einen Namen eingeben, der garantiert nicht in der Liste existiert. Beispielsweise „xyz“. Dann erkennen Sie auch, dass das Programm Sie darauf aufmerksam macht und naturgemäß keine Daten überträgt. Aber das mit dem „naturgemäß“ ist so eine Sache …
Geben Sie in die InputBox doch einfach einmal bar ein, Groß- oder Kleinschreibung ist egal. Dann klingelt das Telefon und ganz in Gedanken klicken Sie auf OK oder drücken die Taste Eingabe. Und es kommt keine Fehlermeldung, was Sie vielleicht überrascht, schließlich gibt es keine/n Abgeordneten mit dem Namen. Noch größer wird vielleicht die Überraschung, wenn Sie sich das Blatt Ziel ansehen. Ein Dutzend Namen!
Rasch werden Sie erkennen, dass das Programm alle Datensätze herausgesucht hat, welche die Buchstabenkombination des Suchbegriffs im Namen enthalten. Egal ob zu Beginn oder irgendwo im Namen. Das kann in manchen Fällen ein Vorteil sein, hier ist es eher von Nachteil, denn es wäre unter Umständen einiges an Nacharbeit fällig, wenn nur die exakten Begriffe hätten kopiert werden sollen.
Eine wirklich kleine Änderung des Codes bringt Abhilfe:
Set rngFound = .Find(what:=strSuch)Set rngFound = .Find(what:=strSuch, LookAt:=xlWhole)
Sie ergänzen also die Zeile um eine weitere Anweisung. Und wenn Sie jetzt bar oder Bar eingeben, dann kommt wirklich die Fehlermeldung, dass der Name nicht existiert. Und damit Sie es nicht so schwer haben 😛 , finden Sie hier den fertigen Code.
Joker sind möglich
Probieren Sie den eben geänderten Code doch einmal mit dem Namen Müller. Und Sie sehen, das klappt wunderbar. Zwei Abgeordnete wurden gefunden. Aber gemäß dem Motto, dass Vertrauen gut ist, Kontrolle aber besser schauen Sie noch einmal in die Quelldaten und suchen die Müllers. Hmmm, das sind ja eigentlich einige mehr. Es sind 7 Namen, wenn ich den Doppelnamen (Müller-Gemmeke) dazu rechne.
Des Rätsels Lösung: Geben Sie den Suchnamen mit einem Joker ein: Müller*. Der Stern steht dabei für beliebig viele Zeichen die folgen können aber nicht müssen. Probieren Sie es aus und der Erfolg zeigt sich sofort. Und das geht natürlich auch, wenn Sie beispielsweise nur wissen, dass der Name mit La* beginnt.
Falls Sie durch verschiedene Versuche etwas genervt sind, weil Sie jedes Mal der besseren Kontrolle wegen die Ergebnisse in der Tabelle Ziel löschen mussten, dann biete ich Ihnen einen kleinen Einzeiler an, den Sie hier kopieren und an den vorhandenen Code anfügen können. Anschließend bei Bedarf einfach :
Sub ErgebnisLoeschen()
Sheets("Ziel").Range("A2:B" & Cells(Rows.Count, 1).End(xlUp).Row).EntireRow.Delete
'Achtung, wenn nur die Überschriften existieren, dann werden auch diese gelöscht
'Orientieren Sie sich daher an dem Code weiter oben (lRowDst ... Max() ...)
End Sub
Einfach vor dem nächsten Probelauf beispielsweise via AltF8 aufrufen und das Tabellenblatt Ziel ist wieder „sauber“. Denn nun kommt die nächste Herausforderung: Sie wissen, dass der Name genau vier Zeichen hat und mit Po beginnt. PO* geht nicht, da würden dann auch fünf- und mehrstellige Namen gefunden werden. Probieren Sie doch einfach einmal PO?? als Suchbegriff. Klappt! Zugegeben, dieses Namensbeispiel ist nicht der Hit. Aber im Bundestag gibt es erstaunlich wenige „Meiers“ aller Schreibweisen. Sonst hätten Sie auf diese Weise nach „Meier“, „Mayer“, „Maier“, „Mayer“ suchen können. Und „Mayr“ oder „Meir“, … werden dann nicht gefunden, denn das Fragezeichen steht für exakt 1 Zeichen. Ach ja, und wenn Sie nicht wissen, wie sich Frau oder Herr Meierbohm, Maierbaum, Mayerbehr oder wie auch immer schreibt, dann würde die Eingabe M??erb* gewiss zum Ziel führen.
Hinweis: Natürlich funktioniert das auch prinzipiell mit Zahlen, beispielsweise Produkt- oder Kundennummern. Mit den Jokern sollten Sie dabei aber bewusst umgehen und das Ergebnis erforderlichenfalls von Hand korrigieren.
Nachtrag: Am 21.11.2017 erfolgte eine Änderung des Codes „Grundausstattung“. Wichtigste Änderungen:
- Der Code kann jetzt auch von dem zweiten Arbeitsblatt aufgerufen werden (war vorher nicht möglich bzw. es erfolgten keine Einträge)
- VBA-Bezug auf die Arbeitsblätter erfolgt mit dem Code-Namen des jeweiligen Blattes, dadurch kann der Blattname verändert werden wie auch die Reihenfolge. Mehr zu diesem Thema hier im Blog.
- Auf dem 2. Blatt bleibt die erste Zeile bestehen, wenn A1 nicht leer ist.
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag Ihrerseits z.B. 3,00 € freuen …