Automatisch eine neue Zeile einfügen

[dusticelli]

Guten Abend liebe Forencommunity,

ich suche schon ein paar Tage im Netz, konnte aber bisher keine adäquate Lösung finden, die ich selbst umzusetzen vermag. Ich hoffe aber, dass das eine  oder andere Excell-Schwergewicht mir vielleicht weiterhelfen kann. Hier mein Problem:

Ich habe eine Tabelle mit Datensätzen aus Bestellungen. Pro Bestellposition gibt es eine Zeile. In einer Spalte jeder Zeile stehen auch die Versandkosten der gesamten Bestellung. Das heisst bei einer Bestellung die mehr als eine Position hat, wiederholen sich die Versandkosten in dieser Spalte für jede Zeile. 

Nun muss ich diese Bestellungen in ein System importieren, das die Versandkosten in einer separaten Zeile benötigt, eben als eigene Bestellposition.

Im Grund muss ich also irgendwie über die Tabelle eine Routine laufen lassen, die am Ende einer Bestellung immer eine neue Zeile einfügt, und diese mit den Versandkosten als Bestellposition füllt.

Soweit ich das sehe, geht das wohl nur über ein Macro, oder VBA (ist das das selbe?). Damit kenne ich mich leider nicht so gut aus. Könnte man das auch irgendwie mit normalen Formeln lösen?

Danke schon mal.

[WillWissen]

Hi,

du solltest eine Beispieltabelle, die vom Inhalt anonymisiert sein sollte, vom Aufbau her aber zu 100% deinem Original entsprechen muss, hochladen. Wie das geht, kannst du hier http://www.clever-excel-forum.de/Thread-...ng-stellen  nachlesen.

[dusticelli]

Hallo WillWissen,

Ok, sorry. Ich hatte es schon im Beitrag per html versucht, aber das wurde ignoriert. Aber so ist es ja noch viel besser. Hier kommt also eine Musterdatei, natürlich anonymisiert. Hier nochmal zur Info:

Jedes mal wenn in Spalte B eine neue Rechnungsnummer kommt, sollte davor eine neue Zeile ergänzt werden. 

Diese neue Zeile wird dann befüllt mit den Daten aus der Zeile zuvor ( 1:1 wo die globalen Rechnungsdaten stehen, und als Artikel [Spalten BR und BS] eben mit statischen Werten für Artikelnummer der Versandkosten und Beschreibung  [z.B. VERSAND | Versandkostenanteil] und dem eben dem Preis [in Spalte BV] der aus Spalte AX stammt. )

hoffe das ist jetzt einigermaßen verständlich

[Edit]
Ich hänge nochmal eine verkürzte Version an, ist vielleicht besser. Und hier mal ein Screenshot:




Nach Zeile 2, 4, 5 und 6 brauche ich jeweils eine neue Zeile, die in den Spalten "D" und "E" die Werte "VERSAND" und "Versandkostenanteil" enthält und in Spalte "G" den Preis aus Spalte "H" darüberliegend.

[dusticelli]

Mensch, das ist aber schwierig. Also ich habe inzwischen für mich folgende Strategie als Workaround entwickelt, trotzdem gelingt mir die Umsetzung nicht. 

Das schwierigste ist ja die neue Zeile unter oder über jeden Wechsel einer Rechnungsnummer zu setzen. Jetzt habe ich gedacht, ich nehme schon mal eine Hilfsspalte und markiere die entsprechenden Zeilen durch eine "Wenn(" Bedingung. Etwa an meinem verkürzten Beispiel, füge ich links neben Spalte A eine neue Spalte ein, die ich wie folgt befülle

=Wenn(B2<>B3;"WAHR";"")

Dann lasse ich diese Formel einmal über alle Zeilen der Spalte laufen und habe immer vor (bzw. nach) einem Wechsel der Rechnungsnummer einen Marker. 

Jetzt dachte ich, ich kann dann wenn diese Zeilen alle markiert sind mit einem Rutsch immer ein Zeile einfügen. Das bekomme ich aber irgendwie nicht hin. Wenn ich also eine Weg wüsste, dass ich auf einmal mehrere Zeilen "immer unterhalb (oder eben oberhalb) einer markierten Zeile" einfüge dann könnte ich mein Problem mit wenigen Schritten lösen.

Gibt es für dieses Vorgehen eine einfach Lösung? Also das gleichzeitige Einfügen einer Zeile ober-/unterhalb einer Markierung?

Danke schon mal für eure Tipps

[schauan]

Hallöchen,

im Prinzip geht das mit einem Makro so. Wichtig für die Datenübernahme ist der Code wo ich aus Spalte H (8) den Wert in die Spalte A (1) übertrage. Wenn Du die Spalten-Zahlen änderst, kannst Du auch andere Werte übertragen. Die eine Codezeile kann man auch mehrmals untereinader schreiben und dann mehrere Werte in die neue Zeile holen. Hast Du schon mal was mit Makros gemacht?

Code:

Sub ZeileDazu()
Dim iCnt
iCnt = 2
'Mach es solange in Zelle icnt in Spalte A etwas anderes als nix steht
Do While Cells(iCnt, 1) <> ""
'Mit der nächsten Zelle
With Cells(iCnt + 1, 1)
  'Wenn was anderes drin steht, dann
  If .Value <> Cells(iCnt, 1) Then
    'Zeile hinzufuegen
    Rows(iCnt + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    'In Spalte A der neuen Zeile Wert aus Spalte H der alten Zeile einfuegen
    Cells(iCnt + 1, 1).Value = Cells(iCnt, 8).Value
    'Zeilenzaehler hochsetzen
    iCnt = iCnt + 1
  'Ende Wenn was anderes drin steht, dann
  End If
'Ende Mit der nächsten Zelle
End With
'Zeilenzaehler hochsetzen
iCnt = iCnt + 1
'Ende Mache solange in Spalte A etwas anderes als nix steht
Loop
End Sub

[dusticelli]

Hallo schauan,

das klappt ja mit meinem verkürzten Muster wie geölt. Vielen Dank.

Jetzt muss ich das nur noch auf meine Orginaldatei übersetzen. Hoffe das gelingt mir. 

Womit ich noch totale Probleme habe ist die Syntax in VBA um die einzelnen Zellen (bzw. Zeilen oder Spalten) zu identifizieren. Aus Deinem Code

steht hier die 1

Do While Cells(iCnt, 1) <> ""

für Spalte 1 (also A) und iCnt ist die Zelle, und die wurde ja eine Zeile vorher als 2 definiert?

[schauan]

Hallöchen,

ja, am Anfang stimmt das. Weiter unten im Code wird dann ja der Zähler iCnt hochgezählt. Wenn in der Schleife zwischen Do... und Loop nichts eingefügt wird, dann zählt nur die untere Codezeile hoch (icnt=icnt+1). Wird eine Zeile eingefügt, dann wird auch in dem Codebereich eine weitere Zeile dazugezählt - für die "neue".

Die Spaltenzahl kannst Du je einfach per Formel im Blatt ermitteln und dann im Code einsetzen.

[dusticelli]

Hallo schauan,

vielen Dank nochmal. Das ist ja der Hammer. Ich hab den Code noch ein wenig ergänzt, und jetzt flutscht der in einem Rutsch meine ganze Tabelle mit rund 2000 Zeilen runter und modelt sie so um wie ich es brauche. Spitze. Und ich so mit meinem Formeln.. :19:

Sehr coole Sache das mit dem Makro...

[dusticelli]

Hallo Leute,

mein Makro funktioniert super. Es sieht mittlerweile so aus:

Code:

Sub ZeileDazu()
Dim iCnt
iCnt = 2
'Mach es solange in Zelle icnt in Spalte A etwas anderes als nix steht
Do While Cells(iCnt, 2) <> ""
'Mit der nächsten Zelle
With Cells(iCnt + 1, 2)
 'Wenn was anderes drin steht, dann
 If .Value <> Cells(iCnt, 2) Then
   'Zeile hinzufuegen
   Rows(iCnt + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
   'In Spalte A der neuen Zeile Wert aus Spalte H der alten Zeile einfuegen
 Cells(iCnt + 1, 2).Value = Cells(iCnt, 2).Value
 Cells(iCnt + 1, 4).Value = Cells(iCnt, 4).Value
 Cells(iCnt + 1, 7).Value = Cells(iCnt, 7).Value
 Cells(iCnt + 1, 30).Value = Cells(iCnt, 30).Value
 Cells(iCnt + 1, 70).Value = "V-001"
 Cells(iCnt + 1, 71).Value = "Versandkostenanteil"
 Cells(iCnt + 1, 73).Value = "1"
 Cells(iCnt + 1, 74).Value = Cells(iCnt, 50).Value
   'Zeilenzaehler hochsetzen
   iCnt = iCnt + 1
 'Ende Wenn was anderes drin steht, dann
 End If
'Ende Mit der nächsten Zelle
End With
'Zeilenzaehler hochsetzen
iCnt = iCnt + 1
'Ende Mache solange in Spalte A etwas anderes als nix steht
Loop
End Sub

Jetzt ist mir allerdings aufgefallen, dass ich an anderer Stelle meiner Quelldatei noch eine Spalte habe, in der gelegentlich ein Rabatt erscheint. Den ich nun genauso in eine Zeile verfrachten. Jetzt bin ich nicht so sicher wie da der richtige Weg ist, weil ich nicht weiß wie das mit dem Zeilenzähler geht. Ich würde das am liebsten so lösen, dass nur dann, wenn das Rabattfeld >0 ist, eine weitere Zeile eingefügt wird. Meine Idee dazu

Code:

Sub ZeileDazu()
Dim iCnt
iCnt = 2
'Mach es solange in Zelle icnt in Spalte A etwas anderes als nix steht
Do While Cells(iCnt, 2) <> ""
'Mit der nächsten Zelle
With Cells(iCnt + 1, 2)
 'Wenn was anderes drin steht, dann
 If .Value <> Cells(iCnt, 2) Then
   'Zeile hinzufuegen
   Rows(iCnt + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
   'In Spalte A der neuen Zeile Wert aus Spalte H der alten Zeile einfuegen
 Cells(iCnt + 1, 2).Value = Cells(iCnt, 2).Value
 Cells(iCnt + 1, 4).Value = Cells(iCnt, 4).Value
 Cells(iCnt + 1, 7).Value = Cells(iCnt, 7).Value
 Cells(iCnt + 1, 30).Value = Cells(iCnt, 30).Value
 Cells(iCnt + 1, 70).Value = "V-001"
 Cells(iCnt + 1, 71).Value = "Versandkostenanteil"
 Cells(iCnt + 1, 73).Value = "1"
 Cells(iCnt + 1, 74).Value = Cells(iCnt, 50).Value
 
 
 
'mein erweiterter Code geht hier rein
'wenn die Rabattspalte größer als 0
If .Value >0 Cells(iCnt, 47)
'weitere Zeile einfügen
Rows(iCnt + 2).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
'In Spalte X der neuen Zeile Wert aus Spalte Y der alten Zeile einfuegen
Cells(iCnt + 2, 2).Value = Cells(iCnt, 2).Value
Cells(iCnt + 2, 4).Value = Cells(iCnt, 4).Value
Cells(iCnt + 2, 7).Value = Cells(iCnt, 7).Value
Cells(iCnt + 2, 30).Value = Cells(iCnt, 30).Value
Cells(iCnt + 2, 70).Value = "RABATT"
Cells(iCnt + 2, 71).Value = "Rabatt"
Cells(iCnt + 2, 73).Value = "1"
Cells(iCnt + 2, 74).Value = Cells(iCnt, 47).Value

 
 
 
   'Zeilenzaehler hochsetzen
   iCnt = iCnt + 1
 'Ende Wenn was anderes drin steht, dann
 End If
'Ende Mit der nächsten Zelle
End With
'Zeilenzaehler hochsetzen
iCnt = iCnt + 1
'Ende Mache solange in Spalte A etwas anderes als nix steht
Loop
End Sub

Ich denke jetzt muss aber an der Stelle wo der Zeilenzähler hochgesetzt wird eine weitere If-Schleife mit else a la

Wenn Spalte 47 > 0 dann 2 Zeilen hochsetzen, sonst 1

Wie sieht so ein Schnipsel in VBA aus, und wäre das überhaupt richtig? Alternativ kann ich natürlich "immer" eine 2te Zeile einfügen (eben meistens mit null) dann bräuchte ich ja keine If schleife beim Zeilenzähler sondern würde immer +2 hochzählen lassen, richtig?

Also, meine Fragen:

Ist die Stelle wo ich meine Erweiterung ansetze grundsätzlich ok?
Wie kann ich die Rabattzeile nur dann einfügen, wenn das entsprechende Feld >0 ist?
Wie kann ich den Zeilenzähler entsprechend reagieren lassen (1 oder 2 Zeilen hochsetzen)?

Und, wie sieht der Code aus, wenn ich den ursprünglichen Wert der in der Spalte 47 steht, in der neuen Zeile mit negativem Vorzeichen haben will?

Ich bedanke mich schon mal für eure Hilfe.

[schauan]

Hallöchen,

also, so geht das nicht.
If .Value >0 Cells(iCnt, 47)

korrekt wäre
If Cells(iCnt, 47).Value >0 Then

Du schreibst das, nachdem gerade Daten übernommen wurden. Jetzt ist die Frage, ob die zusätzlichen Daten dann auch eine Zeile später rein sollen. Dann wäre der korrekte Platz auch nach der Zeile mit dem Hochzählen, also

Code:

...
 Cells(iCnt + 1, 74).Value = Cells(iCnt, 50).Value
   'Zeilenzaehler hochsetzen
   iCnt = iCnt + 1
   If  Cells(iCnt, 47).Value >0 Then
     ... mach mal was
     'Zeilenzaehler mochmal hochsetzen
     iCnt = iCnt + 1
  End If
  End With

Bei machmalwas nimmst Du dann das Einfügen der zusätzlichen Zeile rein und dann noch an die Übernahme der Daten entsprechend dem bisherigen Muster..

für das negative Vorzeichen multiplizierst Du einfach mit -1

Code:

Cells(iCnt + 2, 74).Value = Cells(iCnt, 47).Value * -1