Suchen und Kopieren (3)

Verschieben statt kopieren

Dieses sollte ein absoluter Schnelldurchgang werden, aber wie das Leben so spielt, ist uns doch noch etwas eingefallen, was dem Ganzen ein wenig Würze gibt. Damit Sie auch einmal nach Zahlen suchen können, soll jede bzw. jeder Abgeordnete eine Identifikationsnummer (ID) bekommen.

ID vergeben

Einfach bei 1 beginnen wäre zu langweilig  😉 . Darum möchten wir, dass die ID zwischen 3.000 und 5.000 liegt. Und selbstverständlich darf keine Nummer doppelt vergeben werden. Und -Sie werden es ahnen- beginnend bei 3.000 in Einerschritten ist nun wirklich auch nicht gerade aufregend.

Ein Beispiel, wie Sie das machen könnten, stellen wir Ihnen jetzt vor. Ganz ohne Makros, plain Excel. Dann öffnen Sie bitte wie gehabt die Datei mit den Namen der Abgeordneten, welche Sie auf Ihrem Rechner oder auch hier im Blog finden. Um eine Zufallszahl zu generieren, gibt es mehrere Wege. Wir machen es uns einfach und erstellen erst einmal ein neues Blatt. Dort schreiben wir in die Zelle A1 diese Formel:
=ZUFALLSBEREICH(3000; 5000)
womit eine Zufallszahl im gewollten Bereich generiert wird. Markieren Sie diese Zelle und StrgC, um die Formel zu kopieren. Nun F5 und als Zieladresse (Verweis) geben Sie A1000 ein. Die aktuelle Zelle ist nun A1000. Jetzt StrgShift (Cursor nach oben). Diese Tasten können Sie dann loslassen, weil alles markiert ist (zumindest sein sollte). Jetzt noch die Eingabe-Taste und nach kurzer Rechenzeit steht in den ersten 1.000 Zellen der Spalte A eine Zufallszahl.

Vielleicht fragen Sie sich nun, warum über 300 mehr Berechnungen als wir IDs brauchen? Die Antwort ist recht simpel: Es kann nicht ausgeschlossen werden, dass die eine oder andere Zahl doppelt ist. Darum den Überhang. Wenn Sie aber jetzt gleich dabei gehen und die Dubletten entfernen würden, dann wäre das nicht gut. Denn bei jede Änderung einer Zelle werden die anderen Inhalte der betroffenen Zellen neu berechnet und dadurch könnten dann wiederum neue Duplikate entstehen. Außerdem würde das wegen der ständigen Neuberechnungen geraume Zeit in Anspruch nehmen.

Also: Markieren Sie die gesamte Spalte A, danach StrgC. Nun ein Rechtsklick in den Bereich, wo die Zahlen stehen und fügen Sie den Inhalt der Zwischenablage Als Wert ein. Dadurch werden die Formeln in feste Zahlen umgewandelt. Nun noch Esc, um die „Ameisenkolonne“ zu stoppen. 😛 Jetzt können Sie im Menü Daten bei den Datentools auch den Punkt Duplikate entfernen auswählen. Bei unserem Test wurden immerhin 210 Duplikate entfernt, was die Notwendigkeit des Überhangs unterstreicht.

Wechseln Sie nun in das erste Arbeitsblatt und fügen Sie eine neue Spalte A ein; der Name der Abgeordneten steht danach in Spalte B. Schreiben Sie in A1 die Überschrift ID, falls Ihre Tabelle Überschriften hat oder bekommen soll. Vergewissern Sie sich noch einmal, dass es 642 Datensätze sind und wechseln 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 Tabellenblatt Suchen&Kopieren an passender Stelle, also in die Zelle A1 oder A2 ein.

OK, das war’s. Oder doch nicht? Wenn Sie die bisher gemachten Schritte Revue passieren lassen dann werden Sie vielleicht daran denken, das das Makro Spalte A abfragt und den Namen wissen möchte. Jetzt steht aber in der Spalte kein einziger Name sondern die ID, eine Nummer. Im allerbesten Fall stimmt dann der Text in der MsgBox oder einer Fehlermeldung nicht, und was natürlich noch gar nicht berücksichtigt ist: Es sind nicht mehr drei sondern vier Spalten, die kopiert werden müssen. Dahingehend ist zwingend eine Anpassung notwendig.

Ein weiterer Punkt ist zwar nicht unbedingt erforderlich aber mehr als sinnvoll: Egal, wenn eine InputBox zur Eingabe des Suchbegriffs verwendet wird soll nur ein einziges Mal der Wert abgefragt werden, nicht für ID und Name getrennt. Bei einer Eingabe in der Tabelle könnte man sich auf zwei Zellen mit je einer Bezeichnung einigen, aber so wirklich schick ist das auch nicht. Wenn nämlich ID und Name in den Zellen stehen, beide sind für sich korrekt aber sie passen nicht zusammen, dann gibt es „Kuddelmuddel“.  🙄

Insofern ist es sinnvoll, wenn eine Routine nur auf 1 Zelle oder 1 Eingabezeile zugreifen muss und dann entscheidet, ob dort Text oder eine Zahl eingegeben worden ist. Für Puristen sei angemerkt, dass die Zahl in einer Textbox zwar Text ist, der aus Ziffern besteht aber von VBA durchaus als Zahl interpretiert bzw. in einen numerischen Wert umgewandelt werden kann.

▲ nach oben …

Makro anpassen

Das sind jetzt so viele Änderungen, dass wir den Code hier noch einmal zum direkten Betrachten einfügen. Außerdem können Sie natürlich wir sonst auch das gepackte File hier im Blog herunterladen. Der Code berücksichtigt nur die Eingabe des Wertes in G4. Weiterhin steht Ihnen gleich, wenn es dann um das Verschieben geht, auch eine fertige Tabelle mit den von uns generierten IDs zur Verfügung.

Wenn Sie eben den fertigen Code herunter geladen haben, dann brauchen Sie natürlich nichts mehr anzupassen. Wollen Sie aber schrittweise nachvollziehen, was sich zur letzten Version geändert hat, dann bietet es sich an, dass Sie diese Version wieder in den VBA-Editor einspielen und weiterhin verwenden. 

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)
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 wichtigsten Unterschiede und Ergänzungen:

  • Wir haben den Code in das Modul DieseArbeitsmappe gelegt. Da wir ja jedes Arbeitsblatt einzeln referenziert haben, ist das möglich. Und der Code, welcher in das Modul eines Arbeitsblattes gehört bleibt so übersichtlicher.
  • Die Variable strSuch wurde in vntSuch umbenannt. Der Hintergrund: Das Präfix str steht für „String“, also eine Zeichenkette. vnt symbolisiert den Typ „Variant“, also einen unbestimmten, variablen Datentyp. Es kann ja ein Text oder eine Zahl sein, was gesucht wird.
  • Unter wksSrc = Sheets(… wurde direkt Set wksDst… eingefügt.
  • Im Block With wksDst wurde die Zeile eingefügt, wo bei Bedarf in A1 die Überschrift eingefügt wird. Es wird allerdings nicht geprüft, ob dort drei oder vier Überschriften stehen.
  • Im neuen Block With wksSrc wurde der Bereich rngSuch in einer Entscheidungsstruktur (Iif)neu festgelegt. Ist der Suchbegriff numerisch, dann wird Spalte A durchsucht, sonst die Spalte B
  • Die für das kopieren verantwortliche Zeile wurde so geändert, dass die Zellen bis zur Spalte D kopiert werden. Hier wäre ein geringer Geschwindigkeitsgewinn möglich, wenn die ID gesucht wird; da jedoch die ID einmalig ist, braucht nicht noch einmal danach gesucht zu werden.
  • Die Meldung, dass der gesuchte Wert nicht gefunden wurde, ist so angepasst worden, dass „ID“ und „Name“ korrekt in der MsgBox stehen.

▲ nach oben …

Verschieben, nicht kopieren

Grundsätzlich sollten Sie sich klar darüber werden, was mit „verschieben“ gemeint ist. Vielleicht werden Sie jetzt sagen „Ist doch klar, hier löschen und da einfügen.“ Das stimmt auch so, aber definieren Sie dann bitte „löschen“. Soll nur der Inhalt der Zellen gelöscht werden oder soll die Zeile komplett gelöscht werden, so dass alle Daten darunter nach oben rücken und auch eine neue Zeilennummer bekommen? Daneben gibt es dann ja auch noch die Möglichkeit, dass nur die betroffenen Teile einer oder mehrer Zeilen gelöscht werden, Inhalte in weiter rechts stehenden Spalten der gleichen Zeile bleiben dann unbetroffen.

Unsere Definition: Es soll die ganze Zeile gelöscht werden, damit keine Lücken entstehen. Und um das zu erreichen, bedarf es prinzipiell nur eines einzigen, weiteren 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

Prinzipiell, wie gesagt. Denn wenn mehrere Zeilen gelöscht werden kann das dazu führen, dass der Bildschirm durch die ständige Aktualisierung ziemlich flackert. Das sollte unterbunden werden. Und wenn in dem betroffenen Blatt auch noch Berechnungen vorgenommen werden, dann sollte auch dafür eine Pause eingelegt werden. Suchen Sie in diesem Code nach den jeweils zwei Mal vorkommenden Stichworten Screenupdating und Calculation, dann sehen Sie, wie Sie selber zum Ziel gelangen können.

Und noch etwas ist in diesem Zusammenhang ganz wichtig: Die Fehlerbehandlung. Angenommen, Sie schalten die Berechnung aus und irgendwo im Code tritt durch was auch immer ein Programmfehler auf. Dann bleibt diese Einstellung bestehen, auch wenn Sie Excel schließen, den Computer ausschalten und erst in einem Monat wieder mit Excel arbeiten. Und dann ist das große Nachdenken angesagt, warum keine automatischen Berechnungen stattfinden, wenn ein Wert in einer abhängigen Zelle geändert wird. Hier ist das Stichwort „Error“. Diese Pflichtübung ist auch in dem Code integriert.

Apropos „Fehler“: Überlegen Sie einmal was geschieht, wenn Sie den Datensatz aus Zeile 4 verschieben. Oder probieren Sie es einfach einmal aus, indem Sie A* als Suchkriterium eingeben. Die Konsequenzen, die Lösungsmöglichkeiten dürfen Sie sich gerne selbst überlegen, denn hier geht es ja in erster Linie im das kopieren von Datensätzen und nicht das verschieben.

Ein Wort noch an alle, die gerne die eine oder andere Zeile Code einsparen: Dass das ScreenUpdating nicht unbedingt wieder auf True gesetzt werden muss, ist richtig. Es ist aber auch nicht falsch und kostet praktisch keine Rechenzeit. Und vor allen Dingen: Es ist zukunftssicher, denn auch wenn Microsoft sich in einer späteren Version einfallen lässt, dass diese Funktionalität nach beenden der Prozedur nicht mehr automatisch auf True gesetzt wird, dann haben Sie Ihr Schäflein schon ins Trockene gebracht  😉 .

Hinweis: In den weiteren Beiträgen zu diesem Thema werden wir beim kopieren bleiben, behalten aber die Fehlerbehandlung bei. Sie sollte eigentlich in jedem Code enthalten sein.

War da nicht noch was? Fehlt noch etwas? Richtig, Versprechen solle man einhalten. Darum geben wir Ihnen hier den Link zur neueren Version der Datei. Wir haben für uns, und wirklich nur für unsere Bequemlichkeit den Button auf der ersten Seite gegen eine ActiveX Schaltfläche ausgewechselt. So ist es für uns leichter, die Verknüpfung zur jeweils aktuellen Version des Makros herzustellen. Und dass im Blatt Suchen&Kopieren die Daten in einer Intelligenten Tabelle stehen, ist auch nur ein Anreiz für Sie, damit etwas zu experimentieren.

▲ nach oben …

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