Dieses sollte ein absoluter Schnell­durch­gang wer­den, aber wie das Leben so spielt, ist uns doch noch etwas einge­fall­en, was dem Ganzen ein wenig Würze gibt. Damit Sie auch ein­mal nach Zahlen suchen kön­nen, soll jede bzw. jed­er Abge­ord­nete eine Iden­ti­fika­tion­snum­mer (ID) bekom­men.

ID vergeben

Ein­fach bei 1 begin­nen wäre zu lang­weilig  😉 . Darum möcht­en wir, dass die ID zwis­chen 3.000 und 5.000 liegt. Und selb­stver­ständlich darf keine Num­mer dop­pelt vergeben wer­den. Und ‑Sie wer­den es ahnen- begin­nend bei 3.000 in Ein­er­schrit­ten ist nun wirk­lich auch nicht ger­ade aufre­gend.

Ein Beispiel, wie Sie das machen kön­nten, stellen wir Ihnen jet­zt vor. Ganz ohne Makros, Plain Excel. Dann öff­nen Sie bitte wie gehabt die Datei mit den Namen der Abge­ord­neten, welche Sie auf Ihrem Rech­n­er oder auch hier im Blog find­en. Um eine Zufall­szahl zu gener­ieren, gibt es mehrere Wege. Wir machen es uns ein­fach und erstellen erst ein­mal ein neues Blatt. Dort schreiben wir in die Zelle A1 diese Formel:
=ZUFALLSBEREICH(3000; 5000)
wom­it eine Zufall­szahl im gewoll­ten Bere­ich gener­iert wird. Markieren Sie diese Zelle und StrgC, um die Formel zu kopieren. Nun F5 und als Zieladresse (Ver­weis) geben Sie A1000 ein. Die aktuelle Zelle ist nun A1000. Jet­zt StrgShift↑ (Cur­sor nach oben). Diese Tas­ten kön­nen Sie dann loslassen, weil alles markiert ist (zumin­d­est sein sollte). Jet­zt noch die Eingabe-Taste und nach kurz­er Rechen­zeit ste­ht in den ersten 1.000 Zellen der Spalte A eine Zufall­szahl.

Vielle­icht fra­gen Sie sich nun, warum über 300 mehr Berech­nun­gen als wir IDs brauchen? Die Antwort ist recht sim­pel: Es kann nicht aus­geschlossen wer­den, dass die eine oder andere Zahl dop­pelt ist. Darum den Über­hang. Wenn Sie aber jet­zt gle­ich dabei gehen und die Dublet­ten ent­fer­nen wür­den, dann wäre das nicht gut. Denn bei jede Änderung ein­er Zelle wer­den die anderen Inhalte der betrof­fe­nen Zellen neu berech­net und dadurch kön­nten dann wiederum neue Dup­likate entste­hen. Außer­dem würde das wegen der ständi­gen Neu­berech­nun­gen ger­aume Zeit in Anspruch nehmen.

Also: Markieren Sie die gesamte Spalte A, danach StrgC. Nun ein Recht­sklick in den Bere­ich, wo die Zahlen ste­hen und fügen Sie den Inhalt der Zwis­chen­ablage Als Wert ein. Dadurch wer­den die Formeln in feste Zahlen umge­wan­delt. Nun noch Esc, um die „Ameisenkolonne” zu stop­pen. 😛 Jet­zt kön­nen Sie im Menü Dat­en bei den Daten­tools auch den Punkt Dup­likate ent­fer­nen auswählen. Bei unserem Test wur­den immer­hin 210 Dup­likate ent­fer­nt, was die Notwendigkeit des Über­hangs unter­stre­icht.

Wech­seln Sie nun in das erste Arbeits­blatt und fügen Sie eine neue Spalte A ein; der Name der Abge­ord­neten ste­ht danach in Spalte B. Schreiben Sie in A1 die Über­schrift ID, falls Ihre Tabelle Über­schriften hat oder bekom­men soll. Vergewis­sern Sie sich noch ein­mal, dass es 642 Daten­sätze sind und wech­seln Sie dann wieder zur Tabelle mit den eben erzeugten IDs. Hier markieren Sie in Spalte A 642 Zellen, kopieren diese und fügen sie in in das Tabel­len­blatt Suchen&Kopieren an passender Stelle, also in die Zelle A1 oder A2 ein.

OK, das war’s. Oder doch nicht? Wenn Sie die bish­er gemacht­en Schritte Revue passieren lassen dann wer­den Sie vielle­icht daran denken, das das Makro Spalte A abfragt und den Namen wis­sen möchte. Jet­zt ste­ht aber in der Spalte kein einziger Name son­dern die ID, eine Num­mer. Im allerbesten Fall stimmt dann der Text in der Msg­Box oder ein­er Fehler­mel­dung nicht, und was natür­lich noch gar nicht berück­sichtigt ist: Es sind nicht mehr drei son­dern vier Spal­ten, die kopiert wer­den müssen. Dahinge­hend ist zwin­gend eine Anpas­sung notwendig.

Ein weit­er­er Punkt ist zwar nicht unbe­d­ingt erforder­lich aber mehr als sin­nvoll: Egal, wenn eine Input­Box zur Eingabe des Such­be­griffs ver­wen­det wird soll nur ein einziges Mal der Wert abge­fragt wer­den, nicht für ID und Name getren­nt. Bei ein­er Eingabe in der Tabelle kön­nte man sich auf zwei Zellen mit je ein­er Beze­ich­nung eini­gen, aber so wirk­lich schick ist das auch nicht. Wenn näm­lich ID und Name in den Zellen ste­hen, bei­de sind für sich kor­rekt aber sie passen nicht zusam­men, dann gibt es „Kud­del­mud­del”.  🙄

Insofern ist es sin­nvoll, wenn eine Rou­tine nur auf 1 Zelle oder 1 Eingabezeile zugreifen muss und dann entschei­det, ob dort Text oder eine Zahl eingegeben wor­den ist. Für Puris­ten sei ange­merkt, dass die Zahl in ein­er Textbox zwar Text ist, der aus Zif­fern beste­ht aber von VBA dur­chaus als Zahl inter­pretiert bzw. in einen numerischen Wert umge­wan­delt wer­den kann.

▲ nach oben …

Makro anpassen

Das sind jet­zt so viele Änderun­gen, dass wir den Code hier noch ein­mal zum direk­ten Betra­cht­en ein­fü­gen. Außer­dem kön­nen Sie natür­lich wir son­st auch das gepack­te File hier im Blog herun­ter­laden. Der Code berück­sichtigt nur die Eingabe des Wertes in G4. Weit­er­hin ste­ht Ihnen gle­ich, wenn es dann um das Ver­schieben geht, auch eine fer­tige Tabelle mit den von uns gener­ierten IDs zur Ver­fü­gung.

Wenn Sie eben den fer­ti­gen Code herunter geladen haben, dann brauchen Sie natür­lich nichts mehr anzu­passen. Wollen Sie aber schrit­tweise nachvol­lziehen, was sich zur let­zten Ver­sion geän­dert hat, dann bietet es sich an, dass Sie diese Ver­sion wieder in den VBA-Edi­tor ein­spie­len und weit­er­hin ver­wen­den. 

Option Explicit

Sub FindAndCopy2c()
   Dim rngSuch As Range, wksDst As WorkSheet, wksSrc As WorkSheet
   Dim vntSuch As Variant, rngFound As Range
   Dim strFirst As String, FoundAdr As String
   Dim ZeSrc As Integer, ZeDst As Integer, lRow As Long
   Dim i As Integer, ZielOK As Boolean, ZielName As String
   
   ZielName = "Ziel"
   With ThisWorkbook
      For i = 1 To ThisWorkbook.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

   Ausklappen (mehr)Einklappen (weniger)


   Set wksSrc = Sheets("Suchen&Kopieren")
   Set wksDst = Sheets(ZielName)
   
   With wksSrc
      If Trim(wksSrc.Range("G4")) = "" Then
         MsgBox "Die Zelle G4 ist leer, darum kann nicht gesucht werden", vbExclamation, "Fehler"
         Exit Sub
      End If
      vntSuch = .Range("G4")
      .Range("G4").ClearContents
   End With
   With wksDst
      If .Range("A1") = "" Then
         .Cells(1, 1) = "ID"
         .Cells(1, 2) = "Name"
         .Cells(1, 3) = "Vorname"
         .Cells(1, 4) = "Fraktion"
      End If
   End With
   
   With wksSrc
      lRow = .Cells(Rows.Count, 1).End(xlUp).Row
      Set rngSuch = IIf(IsNumeric(vntSuch), .Range("A1:A" & lRow), .Range("B1:B" & lRow))
   End With
	
   With rngSuch
      Set rngFound = .Find(what:=vntSuch, LookAt:=xlWhole)
      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 & ":C" & ZeSrc).Copy wksDst.Cells(ZeDst, 1)
            Set rngFound = .FindNext(rngFound)
         Loop While Not rngFound Is Nothing And rngFound.Address <> strFirst
      Else
         MsgBox IIf(IsNumeric(vntSuch), "Die ID", "Der Name") & "  '" & vntSuch _
          & "'  wurde nicht gefunden!", vbInformation, "Fehleingabe?"
      End If
   End With
End Sub

Die wichtig­sten Unter­schiede und Ergänzun­gen:

• Wir haben den Code in das Mod­ul DieseAr­beitsmappe gelegt. Da wir ja jedes Arbeits­blatt einzeln ref­eren­ziert haben, ist das möglich. Und der Code, welch­er in das Mod­ul eines Arbeits­blattes gehört bleibt so über­sichtlich­er.
• Die Vari­able strSuch wurde in vntSuch umbe­nan­nt. Der Hin­ter­grund: Das Prä­fix str ste­ht für „String”, also eine Zeichen­kette. vnt sym­bol­isiert den Typ „Vari­ant”, also einen unbes­timmten, vari­ablen Daten­typ. Es kann ja ein Text oder eine Zahl sein, was gesucht wird.
• Unter wksSrc = Sheets(… wurde direkt Set wks­Dst… einge­fügt.
• Im Block With wksDst wurde die Zeile einge­fügt, wo bei Bedarf in A1 die Über­schrift einge­fügt wird. Es wird allerd­ings nicht geprüft, ob dort drei oder vier Über­schriften ste­hen.
• Im neuen Block With wksSrc wurde der Bere­ich rngSuch in ein­er Entschei­dungsstruk­tur (Iif)neu fest­gelegt. Ist der Such­be­griff numerisch, dann wird Spalte A durch­sucht, son­st die Spalte B. 
• Die für das kopieren ver­ant­wortliche Zeile wurde so geän­dert, dass die Zellen bis zur Spalte D kopiert wer­den. Hier wäre ein geringer Geschwindigkeits­gewinn möglich, wenn die ID gesucht wird; da jedoch die ID ein­ma­lig ist, braucht nicht noch ein­mal danach gesucht zu wer­den.
• Die Mel­dung, dass der gesuchte Wert nicht gefun­den wurde, ist so angepasst wor­den, dass „ID” und „Name” kor­rekt in der Msg­Box ste­hen.

▲ nach oben …

Verschieben, nicht kopieren

Grund­sät­zlich soll­ten Sie sich klar darüber wer­den, was mit „ver­schieben” gemeint ist. Vielle­icht wer­den Sie jet­zt sagen „Ist doch klar, hier löschen und da ein­fü­gen.” Das stimmt auch so, aber definieren Sie dann bitte „löschen”. Soll nur der Inhalt der Zellen gelöscht wer­den oder soll die Zeile kom­plett gelöscht wer­den, so dass alle Dat­en darunter nach oben rück­en und auch eine neue Zeilen­num­mer bekom­men? Daneben gibt es dann ja auch noch die Möglichkeit, dass nur die betrof­fe­nen Teile ein­er oder mehrer Zeilen gelöscht wer­den, Inhalte in weit­er rechts ste­hen­den Spal­ten der gle­ichen Zeile bleiben dann unbe­trof­fen.

Unsere Def­i­n­i­tion: Es soll die ganze Zeile gelöscht wer­den, damit keine Lück­en entste­hen. Und um das zu erre­ichen, bedarf es prinzip­iell nur eines einzi­gen, weit­eren Befehls im Code:

wksSrc.Range("A" & ZeSrc & ":D" & ZeSrc).Copy wksDst.Cells(ZeDst, 1)
Set rngFound = .FindNext(rngFound)
wksSrc.Rows(ZeSrc).EntireRow.Delete
Loop While Not rngFound Is Nothing And rngFound.Address <> strFirst

Prinzip­iell, wie gesagt. Denn wenn mehrere Zeilen gelöscht wer­den kann das dazu führen, dass der Bild­schirm durch die ständi­ge Aktu­al­isierung ziem­lich flack­ert. Das sollte unter­bun­den wer­den. Und wenn in dem betrof­fe­nen Blatt auch noch Berech­nun­gen vorgenom­men wer­den, dann sollte auch dafür eine Pause ein­gelegt wer­den. Suchen Sie in diesem Code nach den jew­eils zwei Mal vork­om­menden Stich­worten Screenup­dat­ing und Cal­cu­la­tion, dann sehen Sie, wie Sie sel­ber zum Ziel gelan­gen kön­nen.

Und noch etwas ist in diesem Zusam­men­hang ganz wichtig: Die Fehler­be­hand­lung. Angenom­men, Sie schal­ten die Berech­nung aus und irgend­wo im Code tritt durch was auch immer ein Pro­gramm­fehler auf. Dann bleibt diese Ein­stel­lung beste­hen, auch wenn Sie Excel schließen, den Com­put­er auss­chal­ten und erst in einem Monat wieder mit Excel arbeit­en. Und dann ist das große Nach­denken ange­sagt, warum keine automa­tis­chen Berech­nun­gen stat­tfind­en, wenn ein Wert in ein­er abhängi­gen Zelle geän­dert wird. Hier ist das Stich­wort „Error”. Diese Pflichtübung ist auch in dem Code inte­gri­ert.

Apro­pos „Fehler”: Über­legen Sie ein­mal was geschieht, wenn Sie den Daten­satz aus Zeile 4 ver­schieben. Oder pro­bieren Sie es ein­fach ein­mal aus, indem Sie A* als Suchkri­teri­um eingeben. Die Kon­se­quen­zen, die Lösungsmöglichkeit­en dür­fen Sie sich gerne selb­st über­legen, denn hier geht es ja in erster Lin­ie im das kopieren von Daten­sätzen und nicht das ver­schieben.

Ein Wort noch an alle, die gerne die eine oder andere Zeile Code eins­paren: Dass das ScreenUpdating nicht unbe­d­ingt wieder auf True geset­zt wer­den muss, ist richtig. Es ist aber auch nicht falsch und kostet prak­tisch keine Rechen­zeit. Und vor allen Din­gen: Es ist zukun­ftssich­er, denn auch wenn Microsoft sich in ein­er späteren Ver­sion ein­fall­en lässt, dass diese Funk­tion­al­ität nach been­den der Proze­dur nicht mehr automa­tisch auf True geset­zt wird, dann haben Sie Ihr Schäflein schon ins Trock­ene gebracht  😉 .

Hin­weis: In den weit­eren Beiträ­gen zu diesem The­ma wer­den wir beim kopieren bleiben, behal­ten aber die Fehler­be­hand­lung bei. Sie sollte eigentlich in jedem Code enthal­ten sein.

War da nicht noch was? Fehlt noch etwas? Richtig, Ver­sprechen solle man ein­hal­ten. Darum geben wir Ihnen hier den Link zur neueren Ver­sion der Datei. Wir haben für uns, und wirk­lich nur für unsere Bequem­lichkeit den But­ton auf der ersten Seite gegen eine ActiveX Schalt­fläche aus­gewech­selt. So ist es für uns leichter, die Verknüp­fung zur jew­eils aktuellen Ver­sion des Makros herzustellen. Und dass im Blatt Suchen&Kopieren die Dat­en in ein­er Intel­li­gen­ten Tabelle ste­hen, ist auch nur ein Anreiz für Sie, damit etwas zu exper­i­men­tieren.