AW: Makro Zeilen kopieren bis Liste abgearbeitet
13.06.2016 14:13:49
Zwenn
Hallo Juliane,
da Du offenbar verstehen möchtest, was Du machst, möchte ich Dich in Deinem Vorhaben bestärken und auf ein paar Grundlagen eingehen. Dein gestecktes Ziel ist für Deinen Kenntnisstand ziemlich ambitioniert finde ich, weil Du mehrere Baustellen bearbeiten musst, um das gewünschte Ergebnis zu erhalten. Du wirst bei der Lösung einiges lernen.
Zunächst einige Hinweise zu Deinem Makro. Um zu erklären, worauf Du beim schreiben von Quelltext achten solltest, habe ich es zweimal zeilenweise durchnummeriert. Die erste Abbildung ist 1 zu 1 Dein gepostetes Makro. Die zweite Abbildung ist die von mir "berichtigte". Die Berichtigungen beziehen sich dabei ausschließlich auf die Schreibweise, nicht auf die Funktionalität.
Dein Makro:
10 Sub Kopieren()
20
30 Dim Quelle As Worksheet
40 Dim Ziel As Worksheet
50 Dim iRow As Integer
60 Dim dValue As Double
70
80 with this workbook
90 Set Quellblatt = .Worksheets("Quelle")
100 Set Ziel = .Worksheets("Ziel")
110 iRow = 1
120 End With
130
140 While not is empty (cells(iRow,1))
150 With Sheets("Ziel")
160 lr = .Cells(Rows.Count, "A").End(xlUp).Row + 1
170 .Range("A2:A3").EntireRow.Copy .Cells(lr, 1)
180 iRow = iRow + 1
190 Wend
200
210 End With
220
230 End Sub
Berichtigte Schreibweise:
10 Sub Kopieren()
20
30 Dim Quelle As Worksheet
40 Dim Ziel As Worksheet
50 Dim iRow As Integer
60 Dim dValue As Double 'Wird im Code nicht verwendet
70
80 With ThisWorkbook 'ThisWorkbook ohne Freizeichen
90 Set Quellblatt = .Worksheets("Quelle")
100 Set Ziel = .Worksheets("Ziel")
110 End With
120
130 iRow = 1 'gehört nicht mit in den With-Block
'(kein Syntaxfehler, aber für die Übersicht besser)
140
150 While Not IsEmpty(Cells(iRow, 1)) 'IsEmpty ohne Freizeichen
160 With Sheets("Ziel")
170 lr = .Cells(Rows.Count, "A").End(xlUp).Row + 1
180 .Range("A2:A3").EntireRow.Copy .Cells(lr, 1)
190 iRow = iRow + 1
200 End With 'End With gehört in diese Zeile
210 Wend
220
230 End Sub
Die Fett geschriebenen Kommentare weisen auf Zeilen hin, in denen direkt Fehler waren oder die ich verschoben habe. Was Du Dir von Anfang an angewönen solltest, ist das Einrücken von zusammengehörigen Code-Blöcken. Ein Code-Block wird durch einen öffnenden und schließenden Befehl geklammert. Z.B. With - End With, If - Else - End If, Select - End Select, For - Next, usw.
Alles was nach dem öffnenden Befehl kommt, sollte um einen Tab nach rechts eingerückt werden. Auf diese Weise erhältst Du eine einfach Sichtkontrolle, ob Deine Blöcke den richtigen Code enthalten und ob Du sie an der richtigen Stelle geschlossen hast.
Du fragst, was alles als Variable deklariert werden muss. Das ist einfach beantwortet: Alles, was eine Variable ist. Du solltest jedes Modul mit der Zeile Option Explicit einleiten. Dieser Befehl sorgt dafür, dass der Interpreter vor der Ausführung des Codes prüft, ob alle verwendeten Variablen auch deklariert wurden.
Lässt Du Option Explicit weg, verwendet VBA zwar alle Variablen einfach, wo sie das erste mal auftauchen, das hat aber (in meinen Augen) ausschließlich Nachteile. Kleine Tippfehler führen zu großen Suchaktionen, wenn Du z.B. an einer Stelle lFarbWert verwendest, darauf an anderer Stelle wieder zugreifen willst, aber versehentlich lFrabWert schreibst. Option Explicit fängt solche Fehler direkt ab.
Du fragst, wie Du es festlegst, auf welchen 'Reiter' Du zugreifst. Du meinst Tabellen und Du machst das in Deinem kleinen Makro bereits mit der Tabelle Ziel in der Zeile
With Sheets("Ziel")
In Deinem ersten With-Block hast Du allerdings eine ganz andere Deklaration für Deine beiden Tabellenblätter gemacht, die Du anschließend nicht mehr verwendest:
set Quellblatt = ThisWorkbook.worksheets("Quelle")
set Ziel = ThisWorkbook.worksheets("Ziel")
Deklariert hast Du zwei Objektvariablen. Für Deine Zeile With Sheets("Ziel") hättest Du also einfach schreiben können With Ziel. Du greifst aber mit With Sheets("Ziel") anders auf das Objekt Tabelle mit dem Namen Ziel zu.
Das ist sicher erstmal sehr verwirrend, weil Du vermutlich nicht weißt, was eine Objektvariable ist und was denn bitteschön "das Andere" sein soll.
Neben Objektvariablen gibt es in VBA noch die einfachen Datentypen, die alle einen bestimmten Wertebereich haben. Informiere Dich in dieser Übersicht, welche einfachen Datentypen es gibt und wie ihre Wertebereiche sind:
https://www.herber.de/mailing/vb/html/vagrpdatatype.htm
Wenn Du Dir z.B. den einfachen Datentyp Integer anschaust, siehst Du, dass dieser nur Ganzzahlen aufnehmen kann und einen Wertebereich zwischen -32.768 und +32.767 hat. Du verwendest diesen Datentyp als Laufvariable iRow in deiner Schleife, um die Zeilen der Tabelle durchzugehen. Da der größte mögliche Wert von Integer 32.767 ist, würdest Du eine Laufzeit-Fehlermeldung bekommen, sobald Deine Tabelle auch nur eine Zeile mehr hat.
Auch wenn es in den meisten Fällen keine so großen Tabellen sein werden, würde ich, um mit modernen Excel-Tabellen zu arbeiten, ohne solche Überraschungen zu erleben, für solche Variablen immer den Datentyp long nehmen. Dieser kann Werte bis 2.147.483.647 aufnehmen. Da eine Tabelle in Excel ab Version 2007 etwas mehr als 1 Mio Zeilen haben kann, paßt das immer.
Objektvariablen sind nicht ganz so leicht zu verstehen. Im Zusammenhang mit Excel sprechen wir in der Regel von Objekten, wenn wir uns auf Bestandteile der Arbeitsmappe oder der sonstigen Excel-Umgebung beziehen, die bereits in Excel zur verfügung stehen. Eine Arbeitsmappe selbst ist ein Objekt, eine Tabelle ist ein Objekt und auch eine Zelle ist ein Objekt. Daneben gibt es viele weitere.
Gemeinsam ist allen Objekten, dass sie fertige Methoden mitbringen. Eine Methode ist das, was wir in VBA selbst als Function oder Sub schreiben. Nur dass die Methoden von Objekten alle Aufgaben übernehmen, die etwas mit dem Objekt zu tun haben. Um auf eine bestimmte Methode für ein Objekt zuzugreifen, wird der Methodenname in unserem Quelltext mit einem Punkt vom Objektnamen getrennt.
Z.B.
sheets("Tabelle1").name = "Juliane"
Diese Zeile würde den Namen der Tabelle "Tabelle1" in "Juliane" ändern. Dabei ist sheets das Objekt und name die Methode, um etwas mit dem Namen des Objektes anzustellen.
Ich habe als Beispiele für Objekte Arbeitsmappe, Tabelle und Zelle angeführt. Wie Du Dir leicht vorstellen kannst, bilden diese drei eine Hirarchie. Arbeitsmappen bestehen aus Tabellen und Tabellen bestehen aus Zellen. Um nun also auf eine bestimmte Zelle zuzugreifen, können wir Excel genau mitteilen, wo sich diese befindet, indem wir den Punkt-Operator auch auf diese Hierarchie anwenden.
Wenn man etwas vom Hierarchie-Pfad weglässt, dann verwendet Excel automatisch die aktiven Objekte. Bezogen auf Das Beispiel zum Ändern des Tabellennamens bedeutet das, der Name wird nur geändert, wenn es in der Arbeitsmappe, aus der heraus das Makro aufgerufen wurde eine Tabelle mit dem Namen "Tabelle1" gibt. Ansonsten gibt es eine Fehlermeldung. Wenn sich "Tabelle1" in einer zweiten geöffneten Arbeitsmappe befindet kann der Name aus Arbeitsmappe 1 geändert werden, indem vor den sheets-Befehl der Name von Arbeitsmappe 2 angegeben wird.
Meistens benötigt man diesen Mechanismus, um auf verschiedene Tabellen in einer Arbeitsmappe zuzugreifen:
sheets("Tabelle1").cells(1, 1).value = sheets("Tabelle2").cells(2, 1).value
Ruft man ein Makro mit dieser Zeile auf, werden die Objekt-Hierarchien und die Methoden von Excel abgearbeitet. Die Zelle A1 aus dem Tabellenblatt Tabelle1 erhält den Inhalt (value) der Zelle A2 aus Tabellenblatt Tabelle2 zugewiesen.
Da man mit so einem starren Konstrukt in der Regel nix anfangen kann, verwendet man Variablen, um das Ganze dynamisch zu gestalten:
Option Explicit
Sub ZellenKopieren()
Dim i as long
Dim lZeilenQuellTabelle as long
Dim strQuellTabelle as String
Dim strZielTabelle as String
strQuellTabelle = "Horst"
strZielTabelle = "Erwin"
lZeilenQuellTabelle = Sheets(strQuellTabelle).UsedRange.Rows.Count
For i = 1 to lZeilenQuellTabelle
sheets(strZielTabelle).cells(i, 1).value = sheets(strQuellTabelle).cells(i + 1, 1).value
Next i
End Sub
Dieses Makro kopiert die Zellinhalte ab Zeile 2 in Spalte A aus der Tabelle Horst in die Zellen ab Zeile 1 in der Tabelle Erwin. Das alles passiert in der Arbeitsmappe, die beim Start des Makros aktiv war. Natürlich nur, wenn es die Tabellenblätter Horst und Erwin dort gibt und in der Spalte A von Horst auch was drin steht. Fehler werden in diesem kleinen Beispiel nicht abgefangen.
Die Dynamik besteht nun darin, dass zum einen die Tabellenbezüge schnell geändert werden können, indem man ihre Namen Horst und Erwin gegen andere austauscht. Außerdem wird abgefragt, wieviele Zeilen in der Quelltabelle überhaupt verwendet werden und genau so weit läuft die Schleife.
Ich wies Dich darauf hin, Dir die einfachen Datentypen anzusehen. Ebenso zu den Grundlagen der Programmierung gehören Schleifen und Kontrollstrukturen. Diese musst Du unbedingt verstehen, sonst kannst Du keinen funktionierenden Programmfluss gewährleisten.
Schau Dir also an, was es in VBA für Schleifentypen gibt und was der Unterschied zwischen einer Kopfgesteuerten und einer Fußgesteuerten Schleife ist. Außerdem solltest Du Dich mit den Kontrollstrukturen IF und Select Case auseinander setzen. Fang am besten mit der einfachen Entscheidung If an.
Im folgenden Link werden Kopfgesteuerte Schleifen Schleifen mit vorangestellter Bedingungsprüfung genannt und Fußgesteuerte Schleifen Schleifen mit nachgestellter Bedingungsprüfung:
https://de.wikibooks.org/wiki/VBA_in_Excel/_Beispiele_f%C3%BCr_Schleifen
Hier noch ein Link zur Erklärung von Kontrollstrukturen (Da sind auch nochmal die Schleifen erklärt):
http://www.frank-moehle.de/computing/literatur/VBASeminar/VBA%20Seminar%20Steuern%20des%20Programmablaufs.htm
Du kannst auch einfach im VBA Editor den Cursor auf einen Befehl setzen und F1 drücken. Dann bekommst Du die Onlinehilfe für diesen Befehl angezeigt.
Vielleicht hilft Dir diese kleine Einführung Deine Gedanken zu sortieren.
Viele Grüße,
Zwenn