Ein kleines Zwis­chen­spiel, einige kleine Ergänzun­gen zum ersten Teil. Die Daten­ba­sis und auch die bish­er erar­beit­ete Makros bleiben beste­hen, nur Kleinigkeit­en wer­den angepasst.

Basis schaffen

Um die Schritte sin­nvoll dur­char­beit­en zu kön­nen, muss erst ein­mal eine solide Basis geschaf­fen wer­den. Grund­vo­raus­set­zung: Sie haben die bekan­nte Tabelle mit den Quell-Dat­en vor­liegen. Erforder­lichen­falls laden Sie diese hier noch ein­mal herunter und fügen Sie den Code aus dieser gepack­ten Datei ein. Falls noch nicht vorhan­den, fügen Sie in der ersten Tabelle (Suchen&Kopieren) eine Zeile mit den Über­schriften ein. Die zweite Tabelle mit dem Namen Ziel kann, muss aber nicht vorhan­den sein.

▲ nach oben …

Drum prüfe, wer sich ewig bindet …

Wie eben schon erwäh­nt, eine Tabelle mit dem Namen Ziel muss nicht existieren. Zumin­d­est nicht im jet­zi­gen Sta­di­um, denn natür­lich sollen die Dat­en in ein bes­timmtes Blatt kopiert wer­den. Und das Sheet darf auch den definierten Namen Ziel haben, aber im Hin­blick auf kün­ftige Beiträge ist das nicht unbe­d­ingt Pflicht. Hier beste­ht erst ein­mal Kon­sens, dass es der Name Ziel sein wird. Also muss erst ein­mal geprüft wer­den, ob das entsprechende Arbeits­blatt über­haupt in der aktuellen Mappe existiert. Und falls das nicht der Fall ist, muss es angelegt wer­den. Das geschieht mit beispiel­sweise solch ein­er Rou­tine:

Dim ZeSrc As Integer, ZeDst As Integer
Dim i As Integer, ZielOK As Boolean, ZielName As String

ZielName = "Ziel"
With ThisWorkbook
   For i = 1 To .Sheets.Count
      If .Sheets(i).Name = ZielName Then
         ZielOK = True
         Exit For
      End If
   Next i
   If Not ZielOK Then
      .Sheets.Add After:=.Worksheets(Worksheets.Count)
      ActiveSheet.Name = ZielName
   End If
End With

Set rngSuch = Sheets("Suchen&Kopieren").Range("A:A")
Set wksDst = Sheets(ZielName)
With wksDst

Einige erk­lärende Worte sind den­noch erforder­lich. Die grauen Zeilen sind der bish­erige, unverän­derte Code. Alles andere wurde neu einge­fügt oder geän­dert. Der Sicher­heit wegen habe ich mit dem Schlüs­sel­be­griff This­Work­book aus­geschlossen, dass vielle­icht auf eine andere geöffnete Arbeitsmappe zuge­grif­f­en wird. Und ich habe den Namen der Ziel-Tabelle in eine Vari­able einge­fügt, um diesen bei Bedarf ein­fach nur an ein­er einzi­gen Stelle ändern zu kön­nen. Zum testen kön­nen Sie gerne die zweite Tabelle löschen und die Sub FindAndCopy2b aufrufen. Und wenn das Blatt bere­its existiert, wer­den natür­lich keine Dat­en darin gelöscht. Diesen Stand der Dinge in Sachen Code kön­nen Sie hier herunter laden.

Hin­weis: Das Makro Sub Ergeb­nis­Loeschen() ist immer dann dop­pelt vorhan­den, wenn Sie die vorherige Datei weit­er ver­wen­den und nicht bein „Stand Null” ges­tartet sind. Eine der bei­den Proze­duren muss gelöscht wer­den, es kommt son­st zu ein­er Fehler­mel­dung.

▲ nach oben …

Mit Start-Button

Das Makro immer mit beispiel­sweise AltF8 aufzu­rufen ist nicht wirk­lich kom­fort­a­bel. Da bietet sich eine Schalt­fläche doch förm­lich an. Und bei der Gele­gen­heit vielle­icht auch gle­ich fürs löschen 😎 . Wech­seln Sie dazu in das Menü Entwick­ler­tools. Wenn diese bei Ihnen nicht sicht­bar sind, dann schauen Sie in diesem Beitrag nach, da ist vieles erk­lärt. Nun Klick­en Sie auf die Schalt­fläche Ein­fü­gen und danach zweck­mäßiger­weise auf  das But­ton-Sym­bol bei den For­mu­lar-Steuerele­menten. Der Rest ist aus unser­er Sicht selb­sterk­lärend. Und im Ziel-Daten­blatt kön­nen Sie gle­icher­maßen vorge­hen.

▲ nach oben …

Ohne InputBox

Die Input­Box ist ja eine recht ein­fache Möglichkeit, Eingaben abzufra­gen. Aber diese auf­plop­pen­den Fen­ster sind nicht jed­er­manns Sache. Es gibt ja auch die Möglichkeit, den Such­be­griff in eine Zelle zu schreiben und dann mit dem But­ton den Such- und Kopier­vor­gang aufzu­rufen.

Gesagt, getan. Wir haben in E4 den Text Such­be­griff: geschrieben und in F4 soll dieser dann hineingeschrieben wer­den. Das Ganze stellt sich so dar:

Hier wird der Such­be­griff eingegeben

Ach ja, falls Sie in der obi­gen Abbil­dung die Far­bge­bung bzw. die Aufk­lapppfeile irri­tieren oder neugierig gemacht haben: Der besseren Über­sicht hal­ber und auch aus ver­schiede­nen anderen Grün­den arbeit­en wir wo immer es geht mit Intel­li­gen­ten Lis­ten. Die Vorteile sind enorm! Im VBA-Code allerd­ings nutzen wir hier nicht die Vorteile, die daraus möglich wären. Die Kom­pat­i­bil­ität zu älteren Excel-Ver­sio­nen soll erhal­ten bleiben.

Vorge­se­hen ist nun fol­gen­des: Nach einem Klick auf die Schalt­fläche soll geprüft wer­den, ob in F4 etwas drin ste­ht. Fall das so ist, läuft der Rest des bekan­nten Makros ab und der eben eingegebene Inhalt wird wieder gelöscht, damit bei einem verse­hentlichen Klick auf die Schalt­fläche das Makro mit den zulet­zt eingegebe­nen Werten noch ein­mal aus­ge­führt wird. Vor­erst wer­den Sie diesen Code an den vorhan­de­nen anhän­gen. Das ist möglich, weil sich der Name des Makros von den anderen unter­schei­det. – Die Änderun­gen sind alle in der oberen Hälfte, bitte ver­gle­ichen Sie sel­ber.

Anschließend müssen Sie unbe­d­ingt der Schalt­fläche diese neue Proze­dur zuweisen. Dazu bei gedrück­ter Taste Strg ein Klick auf den But­ton, welch­er sich danach deut­lich sicht­bar im Edi­tier­modus befind­et. Nun darauf zeigen, Recht­sklick und im Kon­textmenü den Punkt Makro zuweisen… anklick­en. Unter Umstän­den müssen Sie das öfter ein­mal ver­suchen, das ist etwas „frick­e­lig”. Jet­zt nur noch das eben einge­fügte Makro auswählen und fer­tig ist der Vor­gang.

▲ nach oben …

Suchbereich eingrenzen

Eigentlich ist es eine enorme Ressourcen-Ver­schwen­dung, wenn über eine Mil­lio­nen Zellen durch­sucht wer­den sollen, aber nur knapp über 600 Zellen (in Spalte A) Dat­en enthal­ten. Das soll in diesem let­zten Schritt des Inter­mez­zos bere­inigt wer­den.

Diese bei­den Schritte sind erforder­lich, un das Ziel zu erre­ichen: 

• Fest­stellen, welch­es die let­zte Zeile mit Dat­en ist
• Den zu durch­suchen­den Bere­ich der Vari­ablen rng­Such zuord­nen

Die let­zte belegte Zeile der Spalte A find­en Sie mit der gle­ichen Rou­tine, die Sie zum Erken­nen der ersten freien Zeile in der Ziel-Tabelle nutzen. Nur dass hier nicht noch 1 addiert wird. Und die Fes­tle­gung des Bere­ichs ähnelt der bish­eri­gen sehr:

Set wksSrc = Sheets("Suchen&Kopieren")
With wksSrc
   lRow = .Cells(Rows.Count, 1).End(xlUp).Row
   Set rngSuch = .Range("A:A")
   Set rngSuch = .Range("A1:A" & lRow)
End With
Set wksDst = Sheets(ZielName)

Zum Abschluss dieser Runde kön­nen Sie die aktuelle Ver­sion des Codes noch ein­mal herunter laden, zumin­d­est das Haupt-Makro. Wie immer, natür­lich als *.zip gepackt.

Mehr zum The­ma:
Suchen und Kopieren (1)
Suchen und Kopieren (2)
Suchen und Kopieren (3)
Suchen und Kopieren (4)
Suchen und Kopieren (5)