Suchen und Kopieren (1)

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.

▲ nach oben …

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 ZielTabelle 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.

▲ nach oben …

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.

▲ nach oben …

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.

▲ nach oben …

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.

▲ nach oben …

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.

▲ nach oben …

Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?

Dann würde ich mich über einen Beitrag Ihrerseits z.B. 3,00  freuen …

Dieser Beitrag wurde unter Mit VBA/Makro, Musterlösungen, Suchen und finden, Tabelle und Zelle abgelegt und mit , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.