Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1496to1500
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Makro Zeilen kopieren bis Liste abgearbeitet

Makro Zeilen kopieren bis Liste abgearbeitet
13.06.2016 08:50:23
Juliane
Guten Morgen,
ich habe hier im Forum eine nützlich Funktion gelernt, um ganze Zeile zu kopieren und unten an einer Liste einzufügen (siehe fette Markierung). Die nächste Herausforderung für mich ist es, diese in eine Schleife einzubauen. So etwas habe ich leider noch nie gemacht; aber schon mal recherchiert und soweit es mit eben möglich ist zusammenkopiert: siehe Code unten. Für mich als VBA Neuling ist es aber schwierig von allein zu wissen, was alles an Variablen vorher definiert werden muss. Auch weiß ich immer nicht genau, wie ich mit mehrere Reitern umzugehen habe.
Es wäre super, wenn mir jemand helfen könnte.
Was soll passieren? Im Ziel-Reiter sollen mehrere Zeilen kopiert und unten wieder eingefügt werden. Das soll so lange geschehen, bis im Quellreiter die Liste leer ist. D.h.: im Quellreiter stehen in Spalte A Produkte untereinander. Jetzt soll das Makro sich den ersten Wert der Spalte A in der Quelle nehmen und im Ziel Spalte B) gucken, ob es den Wert schon gibt. Wenn nein, soll er die vorhandene Kopierfunktion der Zeilen ausführen. Un in der jeweils ersten Zeile der durch das Kopieren eingefügten Zele in Spalte B das Produkt (aus Spalte A der Quelle) einfügen. Auf dies Art und Weise soll er die ganze Liste der Quelle durchgehen.
Mir fehlt in dem Code leider noch einiges:
- Wie sage ich ihm das sich iRow auf die Liste im Reiter Quelle bezieht?
- Wie sage ich ihm, dass er nach dem Kopieren der Zeilen auch noch das Produkt da reinschrieben soll?
- usw.
Sub Kopieren()
dim Quelle as worksheet
dim Ziel as worksheet
dim iRow as Integer
dim dValue as Double
with this workbook
set Quellblatt=.worksheets("Quelle")
set Ziel=.worksheets("Ziel")
iRow=1
end with
While not is empty (cells(iRow,1))
With Sheets("Ziel")
lr = .Cells(Rows.Count, "A").End(xlUp).Row + 1
.Range("A2:A3").EntireRow.Copy .Cells(lr, 1)
iRow=iRow+1
Wend
End With
End Sub

21
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Makro Zeilen kopieren bis Liste abgearbeitet
13.06.2016 12:53:18
Juliane
Hallo,
kann mir jemand zumindest teilweise helfen? Ich versuche auch gern mich stückweise dem Thema zu nähern. Nur weiß ich nciht so richtig, mit welchem Puzzlestück ich weiter machen kann. Villeicht hat ja noch jemand einen Tipp? Oder kann mir zumindest sagen, was bei meinem Code noch fehlt bzw. ob bei dem vorahdnenen auch schon was falsches enthalten ist...
VG

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

Anzeige
AW: Makro Zeilen kopieren bis Liste abgearbeitet
13.06.2016 21:44:03
Juliane
Vielen Dank Zwenn für deine ausführlich Erklärung. Ich denke ich verstehe die grundsätzliche Struktur und Art der Schreibweise nun etwas besser.
Dennoch wäre es schön, wenn ich nun etwas über die fehlenden Funktionen:
z. B. Zellinhalt in die jeweils erste Zelle der kopierten Zeile schreiben zum Abarbeiten der Liste im zweiten Reiter erfahren könnte.
Anbei mein aktueller Stand des Codes:
Option Explicit
Sub Kopieren()
Dim Quelle as worksheet
Dim Ziel as worksheet
Dim iRow as Long
With ThisWorkbook
set Quellblatt=.worksheets("Quelle")
set Ziel=.worksheets("Ziel")
End with
iRow = 1
Do While Quellblatt.cells(iRow,1)””
With Ziel
lr = .Cells(Rows.Count, "A").End(xlUp).Row + 1
.Range("A2:A3").EntireRow.Copy .Cells(lr, 1)
iRow=iRow+1
End With
Loop
End With
End Sub

Anzeige
AW: Makro Zeilen kopieren bis Liste abgearbeitet
13.06.2016 22:06:28
Juliane
Ok, ich habe einmal versucht, die neue Funktion: Kopieren des Zellinhalts aus Quelle in Ziel bei jedem Durchlauf mit aufzunehmen. DAdurch ändert sich der Code wie folgt (siehe fett). Ist das logisch und funktional der richtige Weg soweit?
Besten DAnk
Option Explicit
Sub Kopieren()
Dim Quelle as worksheet
Dim Ziel as worksheet
Dim iRowQuelle as Long
Dim iRowZiel as Long
With ThisWorkbook
set Quellblatt=.worksheets("Quelle")
set Ziel=.worksheets("Ziel")
End with
iRowQuelle = 1
iRowZiel=2
Do While Quellblatt.cells(iRow,1)””
With Ziel
lr = .Cells(Rows.Count, "A").End(xlUp).Row + 1
.Range("A2:A3").EntireRow.Copy .Cells(lr, 1)
.cells(2,1).value=Quellblatt.cells(iRow,1).value
iRowQuelle=iRowQuelle+1
iRowZiel=iRowZiel+2
End With
Loop
End With
End Sub

Anzeige
Beispieldatei?
14.06.2016 19:20:05
Michael
Hi zusammen,
mir ist die Geschichte mit den *zwei* Zeilen nicht ganz klar, und die zweite Frage
nach dem Kopieren der Zeilen auch noch das Produkt da reinschrieben
erschließt sich mir auch nicht direkt.
Außerdem sehe ich in den Codeschnipseln nirgends eine Funktion, die überprüft, ob Quelle!Ax in Ziel!By vorhanden ist...
Lade doch mal eine anoymisierte Beispieldatei hoch, samt den Makros, so weit sie vorhanden sind, und insbesondere einem Beispiel, was nun wohin kopiert werden soll.
@Zwenn: ich mische mich mal ein, weil ich den Eindruck habe, daß es nicht recht vorangeht...
Schöne Grüße,
Michael

Anzeige
AW: Beispieldatei?
15.06.2016 08:49:13
Juliane
Hallo Michael,
das wäre super, wenn du mal draufschaust. Ich trete in der Tat auf der Stelle.
Leider klappt es mit dem Upload nicht, Fehlermeldung: Kein Upload möglich Grund: Ungültiger Dateiname
Ich kann das nicht verstehen. Ich habe es sowohl mit xlsx als auch xlsm versucht.
Hier ein Bsp.:
Ziel: Besteht aktuell aus 2 Zeile (2 und 3, wenn in Zeile 1 die Üebrschriften stehen). Diese dienen mit als "Vorlage" und sollen beide bei jedem Durchlauf kopiert werden / neu angelegt werden.
Quelle: Hier ist eine Tabelle. Wichtig ist nur Spalte A, da hier die Produkte drinstehen.
Spalte A
Produkt 1
Produkt 2
Produkt 3
etc.
Diese Liste soll durchlaufen werden bis zur ersten Leerzeile. Bei jedem Durchlauf wird im Ziel das Kopieren der beiden "Vorlagen"-zeilen durchgeführt.
Z.B nach 3 Durchläufen.
Zeile 1 (ins Spalte B steht "Produkt 1")
Zeile 2
Zeile 1 Kopie (ins Spalte B steht "Produkt 2")
Zeile 2 Kopie
Zeile 1 Kopie (in Spalte B steht "Produkt 3")
Zeile 2 Kopie

Anzeige
AW: Beispieldatei?
15.06.2016 10:04:27
Zwenn
Hallo Ihr Zwei,
@Michael
Das ist völlig in Ordnung. Es hat sich ausserhalb des Forums etwas ergeben, dass meine Zeit dieser Tage bindet.
@Juliane
Makros lassen sich nur in .xlsm oder xlsb Dateien speichern. Wenn Du eine Datei hochladen willst, dann darf weder im Dateinamen, noch im Pfad zur Datei ein Freizeichen vorkommen. Sonst wird der Upload abgelehnt. Kopiere die Datei, die Du hochladen willst einfach in das Rootverzeichnis einer Festplatte und gib ihr einen einzigen Buchstaben als Namen. Dann hast Du als Pfad z.B. c:\a.xlsm und kannst die Datei hochladen.
Viele Grüße,
Zwenn

Anzeige
AW: Beispieldatei?
15.06.2016 11:38:38
Juliane
https://www.herber.de/bbs/user/106253.xlsm
Kurze Anmerkung:
Es gibt eine "Ausbaustufe" des Makros. Diese habe ich hier bereits skiziiert, weil ich mich im Anschluss ncoh darum kümmern muss. Ich hatte es im Forum aber noch nicht gepostet, weil ich nicht wusste, wie viel Arbeit in dem Reichtigziehen des ersten Makros steckt.
Aber für euch zum Hintergrund: Das erste Makro ist das von mir beschriebene: Es soll eine Erstbefüllung sein, die 2 Zeilen anlegt und jeweils in die erste der beiden in Spalte B das Produkt aus der abzuabreitenden Liste schreibt.
In der nächsten Ausbaustufe, werde ich noch einen Button "Aktualisierung" brauchen. Dieser wird alle Produkte in der Quelle durchgehen und findet er eines, dass noch nicht im Ziel enthalten ist, dann muss dieses unten in der Liste ergänzt werden.
Ich möchte das über 2 Makros machen, um die Rechenleistung gering zu halten. Denn das 2. Makro wird ja später einen Abgleich über vorhandene / nicht vorhandene Daten machen. Das ist ja quasi eine Schleife mehr und damit "schneller", wenn die Daten schon vorgefüllt sind. Genauso wie Maktro 1 schneller ist, wenn es bei der Erstebefüllung nicht eine zusätzliche Schleife jedes Mal zum Abgleich durchlaufen muss.
Ich hoffe, das ist verständlich?
Ich danke euch 1000Mal

Anzeige
AW: Beispieldatei?
15.06.2016 11:38:45
Juliane
https://www.herber.de/bbs/user/106253.xlsm
Kurze Anmerkung:
Es gibt eine "Ausbaustufe" des Makros. Diese habe ich hier bereits skiziiert, weil ich mich im Anschluss ncoh darum kümmern muss. Ich hatte es im Forum aber noch nicht gepostet, weil ich nicht wusste, wie viel Arbeit in dem Reichtigziehen des ersten Makros steckt.
Aber für euch zum Hintergrund: Das erste Makro ist das von mir beschriebene: Es soll eine Erstbefüllung sein, die 2 Zeilen anlegt und jeweils in die erste der beiden in Spalte B das Produkt aus der abzuabreitenden Liste schreibt.
In der nächsten Ausbaustufe, werde ich noch einen Button "Aktualisierung" brauchen. Dieser wird alle Produkte in der Quelle durchgehen und findet er eines, dass noch nicht im Ziel enthalten ist, dann muss dieses unten in der Liste ergänzt werden.
Ich möchte das über 2 Makros machen, um die Rechenleistung gering zu halten. Denn das 2. Makro wird ja später einen Abgleich über vorhandene / nicht vorhandene Daten machen. Das ist ja quasi eine Schleife mehr und damit "schneller", wenn die Daten schon vorgefüllt sind. Genauso wie Maktro 1 schneller ist, wenn es bei der Erstebefüllung nicht eine zusätzliche Schleife jedes Mal zum Abgleich durchlaufen muss.
Ich hoffe, das ist verständlich?
Ich danke euch 1000Mal

Anzeige
AW: Beispieldatei?
15.06.2016 11:59:09
Juliane
Nachtrag: Der Code, den ich hier gepostet habe ist bei mir noch im Entwurfsstadium, d. h. in einer Word-Datei. In der angehängte Excel ist er noch nicht - in der hier geposteten Variante - zu finden.

Verwirrung
16.06.2016 00:53:04
Michael
Hi Juliane,
ich habe zwar die Makro-Lösung so weit fertig, weiß aber gar nicht, ob ich sie noch hochladen soll, weil Du eine neue Frage aufgemacht hast.
Säch mol Bescheid...
Gruß,
Michael

AW: Verwirrung
16.06.2016 07:38:39
Juliane
guten Morgen,
klar gilt die Frage noch. Relativ dringed sogar, weil ich eifnach nciht damit vorankomme. Welche Frage meinst du, hätte ich neugepostet? Bei meinem Kenntnisstand poste ich ja im Schnitt 4 Fragen pro Tag hier im Forum ;)
Das Problem, so wie mit der Beispieldatei beschreiben, habe ich immer noch. Mein Ansatz dieses zu lösen, ist der Code, den hier eignangs gepostet habe (siehe auch unten). Ich habe gestern nur angemerkt, dass der Code sich nicht in der hochgeladenen Excel befindet. Trotzdem ist das alles was ich (leider) bisher habe.
VG

Anzeige
Aha, na gut,
16.06.2016 12:31:27
Michael
Juliane,
ich wollte nur vermeiden, daß ich da was doppelt mache, was evtl. woanders schon erledigt war...
Hoffentlich behältst wenigstens DU die Übersicht über Deine 4 Fragen täglich, seit Wochen, macht uiuiui...
Also, hier die Datei mit Makros: https://www.herber.de/bbs/user/106282.xlsm
Schöne Grüße,
Michael

AW: Aha, na gut,
16.06.2016 14:35:48
Juliane
Hallo Michael, mega vielen Dank!!! Du bist ein Schatz.
Du wohnst nicht zufällig in der Nähe von Braunschweig, dass ich dir einen Kasten Bier vorbeibringen kann, oder? ;)
Ich stecke heute leider viel in Terminen und morgen auch. Daher habe ich nur kurz in dein Makro geschaut. Es ist ja doch ne Menge an Code noch dazugekommen. Falls es für dich in Orndung ist, würde ich mich dann morgen oder übermorgen wieder bei dir melden. Ich möchte den Code unbedingt noch nachvollziehen und habe dann sicherlich ein paar Verständnisfragen, warum was wie gelöst hast.
Zudem steht ich ja vor der (für mich großen) Herausforderung, den Code aus dem Dummy-xls an meine "große" Originaldatei anzupassen ;)

AW: Aha, na gut,
17.06.2016 08:16:55
Juliane
Guten Morgen,
ich bin eben durch den Code. Und, ich bin ganz froh, das meiste davon kann ich eigentlich ganz gut nachvollziehen. Nochmal vielen Dank an dich.
Unten findest du den Code mit Fragen von mir in fett, die eher meinem Verständnis dienen. Dazu habe ich noch eine funktionale Frage.
Sub Kopieren()
Dim qSh As Worksheet, zSh As Worksheet
Dim zV            ' Vorlage in Ziel, also Zeilen 2+3
Dim qMax&, q&, z& ' unterste Zeile in Quelle, Laufvariablen
' & = as long
Const abZ = 5     ' ab Zeile Nr.
Set qSh = Sheets("Quelle")
Set zSh = Sheets("Ziel")
zV = zSh.Range("A2:G3")  ' hier evtl. Bereich ändern 1. Ich würde gerne die ganze Zeile  _
kopieren und nicht dur den Bereich. Kann ich dann einfach "A:G" schreiben? Oder würdest du davon abraten?
qMax = qSh.Range("A" & qSh.Rows.Count).End(xlUp).Row
z = abZ
For q = 2 To qMax
zV(1, 2) = qSh.Range("A" & q) 2. Was genau macht diese Anweisung?
zSh.Range("A" & z).Resize(2, 7) = zV 3. Was bewirkt das Resize?
z = z + 2
Next
Call rahmen(zSh.Range("A" & abZ).Resize((qMax - 1) * 2, 7))
End Sub

Sub Pruefen()
Dim qSh As Worksheet, zSh As Worksheet
Dim zV As Variant, aZiel As Range, c As Range
Dim qMax&, q&, z&, wo&, k&
Dim abZ&
Set qSh = Sheets("Quelle")
Set zSh = Sheets("Ziel")
zV = zSh.Range("A2:G3")
qMax = qSh.Range("A" & qSh.Rows.Count).End(xlUp).Row
abZ = zSh.Range("A" & zSh.Rows.Count).End(xlUp).Row + 1
If abZ 
Sub 4. Ich habe versucht das zu reproduzieren, habe es aber nicht geschafft. Was genau wird  _
hier abgefragt?
Set aZiel = zSh.Range("B4:B" & abZ) 5. Die Anweisung kann ich leider auch nicht ganz  _
nachvollziehen
z = abZ
For q = 2 To qMax
'  MsgBox aZiel(1) 6. Was bedeutet dieser Hinweis von dir?
Set c = aZiel.Find(qSh.Range("A" & q).Value, zSh.Range("B4"), xlValues, xlWhole)
If c Is Nothing Then
zV(1, 2) = qSh.Range("A" & q)
zSh.Range("A" & z).Resize(2, 7) = zV
z = z + 2
End If
Next
If z = abZ Then
MsgBox "keine neuen Produkte vorhanden"
Else
Call rahmen(zSh.Range("A" & abZ & ":G" & z - 1))
MsgBox (z - abZ) / 2 & " Produkte angefügt"
End If
End Sub
Und nun noch die funktionale Frage: Warum hast du das Einfügen des Rahmens als separate Funktion angelegt? Gibt es nicht einen einfach Weg, dass er die kompletten Zeilen inklusive Format gleich mitkopiert? Hintergrund ist, dass ich leider in meiner Originaldatei nicht nur einfache Rahmen habe, sondern z. T. keine Rahmen oder gestrichelte; und z. T. auch ausgefüllte Zellen .

Erklärung
17.06.2016 14:56:28
Michael
Hi Juliane,
ich arbeite meist lieber mit Arrays als mit Ranges, und bei Arrays haste halt reine Daten ohne Formate - deshalb mache ich die Rahmen extra.
Aber wenn das komplizierter ist, kann man auch den "Range" kopieren und einfügen, dann sind die Rahmen dabei.
1. "A:G" ist nicht, da werden nicht die Zeilen, sondern die SPALTEN kopiert; Du kannst es ja entsprechend erweitern auf A2:Z3 oder so.
2. zV = zSh.Range("A2:G3") ist der Bereich in ein Array gesteckt, das von oben nach unten bzw. links nach rechts nummeriert ist.
Die Zeile

zV(1, 2) = qSh.Range("A" & q)
ersetzt also die Zelle in der 1. Zeile und 2. Spalte des Arrays, entsprechend B2 durch das Produkt, das sich in der q-ten Zeile des Quellsheets befindet.
3. Wenn man ein Array zurückschreibt, muß der Tabellenbereich genauso groß wie das Array sein.
Die Anweisung

zSh.Range("A" & z).Resize(2, 7) = zV
schreibt das Array in Spalte A, Zeile z, und zwar mit der GRÖSSE, die im Resize angegeben wird: 2 Zeilen, 7 Spalten. Die 7 änderst Du bitte auch, wenn Du unter 1. einen anderen Bereich nimmst.
Die "geht immer" Variante ist,

zSh.Range("A" & z).Resize(2, ubound(zV,2)) = zV
zu verwenden.
Ubound(Array) oder Ubound(Array,1) gibt die Anzahl der Zeilen und Ubound(Array,2) der Spalten eines Arrays zurück.
Ich habe jetzt zu tun, aber wenn ich es schaffe, bastel ich Dir die range-Variante, da gibt es denn auch weniger Rechnerei.
4./5. wird hier ziemlich komisch angezeigt...
abZ = zSh.Range("A" & zSh.Rows.Count).End(xlUp).Row + 1
sucht die unterste Zeile und "+1" ist dann die nächste leere Zeile.
Set aZiel = zSh.Range("B4:B" & abZ)
aZiel ist als Range deklariert, und hier wird der Bereich der Variablen aZiel zugewiesen, und zwar im ZielSheet zSh von B4 runter bis zur ermittelten Zeile - so ist das "dynamisch", so daß es funktioniert, egal, wie viele Zeilen da sind.
6. kannste rauslöschen, die Zeile ist eh auskommentiert: das sind so Relikte, wo ich mir während der Programmierung manchmal was ausgeben lasse, um zu sehen, ob es richtig läuft...
Schöne Grüße,
Michael

AW: Erklärung
22.06.2016 16:36:48
Juliane
Hallo Michael,
ich habe mir gerade deine Erklärungen durchgelesen.Danke dafür; ein paar SAchen sind mir jetzt deutlicher klar.
Trotzdem nochmal die Frage zu:
Set aZiel = zSh.Range("B4:B" & abZ)
Ich dachte eine Range besteht immer aus Spalte plus Zeile
z. B. qMax = qSh.Range("A" & qSh.Rows.Count).End(xlUp).Row
oder
zV = zSh.Range("A2:G3")
Also quasi immer 2 Bestandteile, die immer die Info zu Zeile und Spalte kombinieren.
Habe ich bei ".Range("B4:B" & abZ)" nicht 3 Bestandteile "B4", "B" und "abz"?
Das was du meinstes, was wohl bei 4. und 5. nciht richtig angezzeigt wurde, ist die Zeile:
If abZ < 5 Then MsgBox "Keine Daten vorhanden": Exit
Ich verstehe nicht, ob ich das wirklich brauche, da ich diesen "Fall" nich reproduzieren konnte.
So, nacher schaue ich mir mal deinen neuen Code an.
LG

jetzt mit Rahmen
17.06.2016 18:27:20
Michael
Hi Juliane,
ich habe das Array durch ein Range ersetzt, dann geht es so:
Option Explicit
Const Bereich = "A3:J4" ' hier evtl. Bereich ändern
Const abZ = 6     ' ab Zeile Nr.
Sub Kopieren()
Dim qSh As Worksheet, zSh As Worksheet
Dim zV As Range   ' Vorlage in Ziel
Dim qMax&, q&, z& ' unterste Zeile in Quelle, Laufvariablen
' & = as long
Set qSh = Sheets("Quelle")
Set zSh = Sheets("Ziel")
Set zV = zSh.Range(Bereich)
qMax = qSh.Range("A" & qSh.Rows.Count).End(xlUp).Row
z = abZ
For q = 2 To qMax
zV.Copy zSh.Range("A" & z)
zSh.Range("B" & z) = qSh.Range("A" & q)
z = z + 2
Next
End Sub
Sub Pruefen()
Dim qSh As Worksheet, zSh As Worksheet
Dim zV As Range, aZiel As Range, c As Range
Dim qMax&, q&, z&, wo&, k&, Zabz&
Set qSh = Sheets("Quelle")
Set zSh = Sheets("Ziel")
Set zV = zSh.Range(Bereich)
qMax = qSh.Range("A" & qSh.Rows.Count).End(xlUp).Row
Zabz = zSh.Range("A" & zSh.Rows.Count).End(xlUp).Row + 1
If Zabz 
Wenn noch gar nichts kopiert ist, funktioniert pruefen übrigens genauso wie kopieren und fügt alles ein.
Happy Exceling,
Michael

AW: jetzt mit Rahmen
20.06.2016 21:14:42
Juliane
Hallo Michael,
erstmal vielen lieben Dank für deine Ausführungen. Ich bin bis Donnerstag auf GEschäftsreise. Ich schaue mir deine letzten beiden NAchrichten an, sobald ich die Ruhe dafür habe.
Daher bitte nicht wundern, dass es meinerseits gerade etwas ruhiger ist.
Liebe Grüße

AW: jetzt mit Rahmen
22.06.2016 16:52:48
Juliane
Hallo Michael,
jetzt die 2. Variante funktioniert schon sehr viel besser. Jetzt übernimmt er auch die vorhandenen Formate. Allerdings passieren bei mir noch folgender Fehler:
Er kopiert nur noch eine und nicht mehr wie vorher 2 Zeilen (siehe Anhang)
https://www.herber.de/bbs/user/106429.xlsm

AW: jetzt mit Rahmen
23.06.2016 13:22:55
Juliane
Hallo Michael,
dein Code ist super. Ich habe ihn jetzt mal in mein reales Dokument aufgenommen, um zu schaune, ob ich die ranges und Co. selber anpassen kann. Das aktuelle Dokument habe ich dir angehängt ebenso wie den Code nochmal unten - mit ein paar wenigen Restfragen (meist zum Verständnis).
Bitte probiere doch das Makro selbst mal aus. Von meiner Seite aus gibt es momentan nur 1 Problem: Wenn ich 2 neue Produkte hinzufüge, dann sagt mir MsgBox, dass es 7 gewesen wären.
Unabhängig davon: Siehst du irgendwo Probleme (aus Makro-Sicht), die ich eventuell nicht sehe?
https://www.herber.de/bbs/user/106458.xlsm
Hier nochmal der aktuelle Code inkl. ein paar Fragen in fett von mir:
Option Explicit
Const Bereich = "A8:AX14" ' hier evtl. Bereich ändern
Const abZ = 15 ' ab Zeile Nr.

Sub Kopieren()
Dim qSh As Worksheet, zSh As Worksheet, rSh As Worksheet
Dim zV As Range   ' Vorlage in Ziel
Dim qMax&, q&, z& ' unterste Zeile in Quelle, Laufvariablen
' & = as long
Set qSh = Sheets("Reiter 2")
Set zSh = Sheets("Fzg_Kritische Punkte KW XX")
Set rSh = Sheets("Reiter 1")
Set zV = zSh.Range(Bereich)
qMax = rSh.Range("A" & qSh.Rows.Count).End(xlUp).Row
z = abZ
For q = 2 To qMax
zV.Copy zSh.Range("A" & z)
zSh.Range("B" & z) = qSh.Range("A" & q)
z = z + 7
Next
End Sub


Sub Pruefen()
Dim qSh As Worksheet, zSh As Worksheet, rSh As Worksheet
Dim zV As Range, aZiel As Range, c As Range
Dim qMax&, q&, z&, wo&, k&, Zabz&
Set qSh = Sheets("Reiter 2")
Set zSh = Sheets("Fzg_Kritische Punkte KW XX")
Set zV = zSh.Range(Bereich)
Set rSh = Sheets("Reiter 1")
qMax = rSh.Range("A" & qSh.Rows.Count).End(xlUp).Row
Zabz = zSh.Range("A" & zSh.Rows.Count).End(xlUp).Row + 1
If Zabz 1. ' Ist es richtig, dass hier mit "B15" immer die Zeile ab der einzufügen ist, stehen muss?
Wenn ja, warum kann ich dann nciht statt "B15" lieber "B&abZ" schreiben, wo wir doch abZ oben  _
als konstant = 15 deklariert haben? 
z = Zabz
For q = 2 To qMax
Set c = aZiel.Find(qSh.Range("A" & q).Value, zSh.Range("B15"), xlValues, xlWhole) ' Richtige  _
Zeile? 2. siehe 1. 
If c Is Nothing Then
zV.Copy zSh.Range("A" & z)
zSh.Range("B" & z) = qSh.Range("A" & q)
z = z + 7
End If
Next
If z = Zabz Then
MsgBox "keine neuen Produkte vorhanden"
Else
MsgBox (z - Zabz) / 2 & " Produkt(e) angefügt" Was passiert hier? Warum sagt er bei 2  _
neuen Produkten, dass es 7 sind?
End If
End Sub

32 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige