Datensatz ändern Excel-Makro ausführen - Excel-Tipps

Dies ist der 19. wöchentliche Excel-Tipp auf.com. Viele der Excel-Tipps beinhalten eine Art Makrotrick. Diese Woche biete ich Excel-Benutzern, die noch nie ein Makro geschrieben haben, eine Einführung in das Aufzeichnen und anschließende Anpassen eines nützlichen Excel-Makros an.

Beispieladressdaten

Angenommen, Sie haben 400 Zeilen mit Adressdaten, wie in der oberen Abbildung links dargestellt. Das Namensfeld befindet sich in Spalte A, die Straße in Spalte B und die Stadt in Spalte C.

Ihr Ziel ist es, die Daten in eine einzelne Spalte wie die in der zweiten Abbildung gezeigte zu konvertieren.

Dieses einfache Problem wird verwendet, um zu veranschaulichen, wie ein einfaches Makro aufgezeichnet, geändert und dann ausgeführt wird.

Für Excel 95-Benutzer: Nach dem Aufzeichnen des Makros legt Excel Ihr Makro auf einem Blatt mit dem Namen Modul1 in Ihrer Arbeitsmappe ab. Sie können einfach auf das Blatt klicken, um auf das Makro zuzugreifen.

Obwohl dieses Arbeitsblatt 400 Datensätze enthält, möchte ich ein kleines Stück des Makros aufzeichnen, das sich nur um die erste Adresse kümmert. Das Makro geht davon aus, dass sich der Zellzeiger auf dem Vornamen befindet. Es werden drei leere Zeilen eingefügt. Die Zelle rechts von der ursprünglichen Zelle wird in die Zelle unter der ursprünglichen Zelle kopiert. Die Stadtzelle wird in die Zelle 2 Zeilen unter der ursprünglichen Zelle kopiert. Es sollte dann den Zellenzeiger nach unten bewegen, damit er auf dem nächsten Namen steht.

Der Schlüssel ist, diesen Prozess zu überdenken, bevor Sie ihn aufzeichnen. Sie möchten beim Aufzeichnen des Makros nicht viele Fehler machen.

Setzen Sie also Ihren Zellenzeiger in Zelle A1. Gehen Sie zum Menü und wählen Sie Extras> Makro> Neues Makro aufnehmen. Das Dialogfeld "Makro aufzeichnen" schlägt den Namen "Makro1" vor. Das ist in Ordnung, also drücke OK.

Der Excel-Makrorecorder hat eine sehr dumme Standardeinstellung, die Sie unbedingt ändern müssen, damit dieses Makro funktioniert. Gehen Sie in Excel 95 zu Extras> Makro> Relative Verweise verwenden. Klicken Sie in Excel 97-2003 auf das zweite Symbol in der Symbolleiste "Aufzeichnung beenden". Das Symbol sieht aus wie ein kleines Arbeitsblatt. Eine rote Zelle in C3 zeigt auf eine andere rote Zelle in A3. Das Symbol heißt Relative Referenz. Wenn dieses Symbol aktiviert ist, ist das Symbol von einer bestimmten Farbe umgeben. Das Symbol merkt sich die letzte Einstellung aus der aktuellen Excel-Sitzung, sodass Sie möglicherweise einige Male darauf klicken müssen, um herauszufinden, welche Methode aktiviert ist oder nicht. Verwenden Sie in Excel 2007 Ansicht - Makros - Relative Verweise verwenden.

OK, wir sind bereit zu gehen. Folge diesen Schritten:

Drücken Sie einmal den Abwärtspfeil, um zu Zelle B1 zu gelangen.
Halten Sie die Umschalttaste gedrückt und drücken Sie zweimal auf den Abwärtspfeil, um die Zeilen 2, 3 und 4 auszuwählen
Wählen Sie im Menü Einfügen und dann Zeilen, um drei leere Zeilen einzufügen.
Drücken Sie den Aufwärtspfeil und dann den Rechtspfeil, um zur Zelle B2 zu gelangen.
Drücken Sie Strg X, um Zelle B2 auszuschneiden.
Drücken Sie den Abwärtspfeil, den Linkspfeil und dann Strg V, um in Zelle A2 einzufügen.
Drücken Sie den Aufwärtspfeil, den Rechtspfeil, den Rechtspfeil, Strg X, den Linkspfeil, den Linkspfeil, den Abwärtspfeil, den Abwärtspfeil und die Strg V, um C1 nach A3 zu bewegen.
Drücken Sie zweimal auf den Abwärtspfeil, damit sich der Zellenzeiger jetzt auf dem nächsten Namen in Zeile A5 befindet.
Klicken Sie in der Symbolleiste auf das Symbol "Aufzeichnung beenden", um die Aufzeichnung des Makros zu beenden.

Nun, Sie haben Ihr erstes Makro aufgenommen. Lass uns mal sehen. Gehen Sie zu Extras> Makro> Makros. Markieren Sie in der Liste Macro1 und klicken Sie auf die Schaltfläche Bearbeiten. Sie sollten etwas sehen, das so aussieht.

Sub Macro1() ' ' Macro1 Macro ' Macro recorded 4/18/99 by Reader ' ' ActiveCell.Offset(1, 0).Range("A1:A3").Select Selection.EntireRow.Insert ActiveCell.Offset(-1, 1).Range("A1").Select Selection.Cut ActiveCell.Offset(1, -1).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(-1, 2).Range("A1").Select Selection.Cut ActiveCell.Offset(2, -2).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(2, 0).Range("A1").Select End Sub

Hey, wenn Sie kein Programmierer sind, sieht das wahrscheinlich ziemlich einschüchternd aus. Lass es nicht sein. Wenn Sie etwas nicht verstehen, gibt es ausgezeichnete Hilfe. Klicken Sie mit dem Cursor irgendwo im Schlüsselwort Offset und drücken Sie F1. Vorausgesetzt, Sie haben die VBA-Hilfedatei installiert, wird das Hilfethema für das Schlüsselwort Offset angezeigt. Die Hilfe informiert Sie über die Syntax der Anweisung. Es heißt, dass es Offset ist (RowOffset, ColumnOffset). Immer noch nicht sehr klar? Suchen Sie nach dem grün unterstrichenen Wort "Beispiel" oben in der Hilfe. In den VBA-Beispielen von Excel erfahren Sie, was los ist. Im Beispiel für den Versatz heißt es, dass Sie die Zelle zwei Zeilen unterhalb und drei Zeilen rechts von der aktuellen Zelle aktivieren möchten:

ActiveCell.Offset(3, 2).Activate

OK, das ist also ein Hinweis. Die Versatzfunktion ist eine Möglichkeit, sich in der Excel-Tabelle zu bewegen. Anhand dieser Informationen können Sie sehen, was das Makro tut. Im ersten Versatz (1, 0) haben wir den Zellzeiger nach unten auf A2 verschoben. Im nächsten Versatz haben wir eine Zeile (-1 Zeilen) und mehr als eine Spalte nach oben verschoben. Möglicherweise verstehen Sie nichts anderes im Makro, aber es ist immer noch nützlich.

Kehren Sie zum Excel-Arbeitsblatt zurück. Setzen Sie Ihren Zellenzeiger in Zelle A5. Wählen Sie Extras> Makro> Makros> Makro1> Ausführen. Das Makro wird ausgeführt und Ihre zweite Adresse wird formatiert.

Sie können sagen, dass die Auswahl dieser ganzen langen großen Befehlsfolge schwieriger ist als nur die Formatierung von Hand. OK, dann Extras> Makro> Makros> Optionen. Sagen Sie im Verknüpfungsfeld, dass Strg + w die Tastenkombination für dieses Makro ist. Klicken Sie auf OK und schließen Sie das Makrodialogfeld mit Abbrechen. Wenn Sie jetzt Strg + drücken, wird das Makro ausgeführt. Sie können eine Adresse mit einem einzigen Tastendruck formatieren.

Bist du bereit für die große Zeit? Wie viele Adressen haben Sie noch? Ich habe einige Male Strg gedrückt, also habe ich noch 395 übrig. Gehen Sie zurück zu Ihrem Makro. Wir werden den gesamten Makrocode in eine Schleife setzen. Fügen Sie vor der ersten Zeile des Makrocodes eine neue Zeile mit der Aufschrift "Do Until activecell.value =" "ein. Fügen Sie vor der End Sub-Zeile eine Zeile mit der Aufschrift "Loop" ein. Die Do-Schleife führt alles zwischen der Do- und der Loop-Zeile aus, bis eine leere Zeile angezeigt wird. Das Makro sieht jetzt so aus:

Sub Macro1() ' ' Macro1 Macro ' Macro recorded 4/18/99 by Reader ' ' Do Until ActiveCell.Value = "" ActiveCell.Offset(1, 0).Range("A1:A3").Select Selection.EntireRow.Insert ActiveCell.Offset(-1, 1).Range("A1").Select Selection.Cut ActiveCell.Offset(1, -1).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(-1, 2).Range("A1").Select Selection.Cut ActiveCell.Offset(2, -2).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(2, 0).Range("A1").Select Loop End Sub

Gehen Sie zurück zu Ihrer Excel-Tabelle. Setzen Sie den Zellenzeiger auf den nächsten Namen. Wenn Sie Strg + drücken, formatiert das Makro alle Ihre Datensätze in wenigen Sekunden.

Die Autoren von Excel-Büchern sagen, dass Sie durch Aufzeichnen eines Makros nichts Nützliches tun können. Nicht wahr! Für die Person, die 800 Mal ausschneiden und einfügen musste, ist dieses Makro sehr nützlich. Das Aufnehmen und Anpassen dauerte einige Minuten. Ja, professionelle Programmierer werden darauf hinweisen, dass der Code schrecklich ineffizient ist. Excel fügt eine ganze Reihe von Dingen ein, die nicht dort abgelegt werden müssen. Ja, wenn Sie wissen, was Sie tun, können Sie dieselbe Aufgabe mit der Hälfte der Zeilen ausführen, die in 1,2 Sekunden statt in 3 Sekunden ausgeführt werden. NA UND? 3 Sekunden sind viel schneller als die 30 Minuten, die die Aufgabe gedauert hätte.

Einige weitere Tipps für den Beginn von Makrorecordern:

Das verwendete Apostroph weist auf einen Kommentar hin. Alles nach dem Apostroph wird von VBA ignoriert
Dies ist objektorientierte Programmierung. Die grundlegende Syntax lautet object.action. Wenn ein objektorientierter Compiler Fußball spielen würde, würde er "ball.kick" sagen, um den Ball zu treten. "Selection.Cut" sagt also, dass die aktuelle Auswahl "bearbeitet> geschnitten" werden soll.
Im obigen Beispiel beziehen sich die Bereichsmodifikatoren auf die aktive Zelle. Wenn sich die aktive Zelle in B2 befindet und Sie "ActiveCell.Range (" A1: C3 "). Select" sagen, wählen Sie den Bereich mit 3 Zeilen mal 3 Spalten ab Zelle B2 aus. Mit anderen Worten, Sie wählen B2: D4. Wenn Sie "ActiveCell.Range (" A1 ")" sagen, wird der 1 x 1-Zellenbereich beginnend mit der aktiven Zelle ausgewählt. Das ist unglaublich überflüssig. Dies entspricht der Aussage "ActiveCell.Select".
Speichern Sie Ihre Arbeitsmappe, bevor Sie zum ersten Mal ein Makro ausführen. Auf diese Weise können Sie, wenn ein Fehler auftritt und etwas Unerwartetes passiert, ohne Speichern schließen und zur gespeicherten Version zurückkehren.

Hoffentlich gibt Ihnen dieses einfache Beispiel unerfahrenen Makrorecordern den Mut, ein einfaches Makro aufzuzeichnen, wenn Sie das nächste Mal eine wiederkehrende Aufgabe in Excel ausführen müssen.