Supportnet Computer
Planet of Tech

Supportnet / Forum / Tabellenkalkulation

Zeile einfügen / Nummerierung / Formeln kopieren





Frage

Hi Leute ich hab erneut ein kleines Excelproblem. In der Excelliste sind Zeilen gruppiert und nummeriert. Wenn ich nun zwischen Zeile 5 und 6 eine Zeile einfüge dann soll er die Nummerierung automatisch ändern zu 5 (alte Zeile), 6 (neue Zeile) und 7 (alte Zeile). Kann man über irgend eine Eigenschaft in Excel auch Formeln mit in die neue Zeile ziehen lassen bzw automatisch einfügen lassen? MfG Phil

Antwort 1 von CaroS

Hi,

nein, geht nicht. Höchstens per VBA.

Gruß,
CaroS

Antwort 2 von pumbaa83

Hi

könnte mir da vielleicht einer helfen wie dies per VBA gehen soll und wie ich dieses dann einbinde?

MfG

Phil

Antwort 3 von CaroS

Hallo Phil,

Du kannst ja mal probieren, ob dieses Makro das tut, was Du brauchst. Nach dem Start des Makros wählst Du eine Zeile oder Zelle aus, darunter wird eine neue Zeile eingefügt und in Spalte A der neuen Zeile wird die Formel aus der darüber liegenden Zelle übertragen.

Bitte achte darauf, dass in dieser Formel die richtigen relativen und absoluten Zellbezüge verwendet werden, damit die automatische Anpassung der Formel zu dem gewünschten Ergebnis führt.

Sub Neue_Zeile_mit_Formel()
Dim r As Range

Set r = Application.InputBox("Bitte eine Zelle oder Zeile auswählen, " & "unterhalb der eine neue Zeile eingefügt werden soll." & Chr(10) & Chr(10) & "In Spalte A wird die Formel aus der alten in die neue Zeile übertragen.", "Neue Zeile einfügen " & _
"unterhalb von:", Selection.Address, Type:=8)

If r.Rows.Count > 1 Then Exit Sub

r.Offset(1, 0).EntireRow.Insert
ActiveSheet.Cells(r.Row, 1).Copy
ActiveSheet.Cells(r.Row + 1, 1).PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
Application.CutCopyMode = False

End Sub


Gruß,
CaroS

Antwort 4 von pumbaa83

Hi CaroS

also das Makro funktioniert super, aber leider nur für eine Zelle.

Ich müsste aber eine ganze Zeile kopieren wo in den einzellnen Zellen auch unterschiedliche Formeln sind.

Könnte man dieses Code dann soweit erweitern das dies realisierbar ist?

MfG

Phil

Antwort 5 von CaroS

Hallo Phil,

ja, kann man. Entweder machst Du es selbst, indem Du für jede zu kopierende Zelle diese 2 Zeilen einfügst und anpasst, wobei S die Nummer der Spalte, gezählt ab A = 1, ist:

ActiveSheet.Cells(r.Row, S).Copy
ActiveSheet.Cells(r.Row + 1, S).PasteSpecial Paste:=xlPasteFormulasAndNumberFormats

Für die Spalten A (= 1), C (= 3) und F (= 6) würde das Makro dann so aussehen:

Sub Neue_Zeile_mit_Formel()
Dim r As Range

Set r = Application.InputBox("Bitte eine Zelle oder Zeile auswählen, " & "unterhalb der eine neue Zeile eingefügt werden soll." & Chr(10) & Chr(10) & "In Spalte A wird die Formel aus der alten in die neue Zeile übertragen.", "Neue Zeile einfügen " & _
"unterhalb von:", Selection.Address, Type:=8)

If r.Rows.Count > 1 Then Exit Sub

r.Offset(1, 0).EntireRow.Insert
ActiveSheet.Cells(r.Row, 1).Copy
ActiveSheet.Cells(r.Row + 1, 1).PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
Rem - - - - - neu eingefügt - - - - -
ActiveSheet.Cells(r.Row, 3).Copy
ActiveSheet.Cells(r.Row + 1, 3).PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
ActiveSheet.Cells(r.Row, 6).Copy
ActiveSheet.Cells(r.Row + 1, 6).PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
Rem - - - - - neu eingefügt - - - - -
Application.CutCopyMode = False

End Sub

Oder Du schreibst auf, in welchen Spalten Formeln übernommen werden sollen, wo bestimmte Werte usw. Wenn Werte übernommen werden sollen, dann geht das mit xlPasteValuesAndNumberFormats anstelle von Paste:=xlPasteFormulasAndNumberFormats. Falls Formatierungen eine Rolle spielen, schreib das ebenfalls dazu, wo und wo nicht.

Gruß,
CaroS

Antwort 6 von JoeKe

Hallo,

warum nicht einfach:

Option Explicit

Sub Neue_Zeile_mit_Formel()
Dim r As Range

Set r = Application.InputBox("Bitte eine Zelle oder Zeile auswählen, " & "unterhalb der eine neue Zeile eingefügt werden soll." & Chr(10) & Chr(10) & "In Spalte A wird die Formel aus der alten in die neue Zeile übertragen.", "Neue Zeile einfügen " & _
"unterhalb von:", Selection.Address, Type:=8)

If r.Rows.Count > 1 Then Exit Sub

r.Offset(1, 0).EntireRow.Insert
ActiveSheet.Rows(r.Row).Copy
ActiveSheet.Rows(r.Row+1).PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
Application.CutCopyMode = False

End Sub


Gruß

JöKe

Antwort 7 von JoeKe

Sub Neue_Zeile_mit_Formel()
Dim r As Range

Set r = Application.InputBox("Bitte eine Zelle oder Zeile auswählen, " & "unterhalb der eine neue Zeile eingefügt werden soll." & Chr(10) & Chr(10) & "In Spalte A wird die Formel aus der alten in die neue Zeile übertragen.", "Neue Zeile einfügen " & _
"unterhalb von:", Selection.Address, Type:=8)

If r.Rows.Count > 1 Then Exit Sub

r.Offset(1, 0).EntireRow.Insert
ActiveSheet.Rows(r.Row).Copy
ActiveSheet.Rows(r.Row+1).PasteSpecial Paste:=xlPasteFormulasAndNumberFormats

Application.CutCopyMode = False

End Sub

so sollte es sein ;-)

Antwort 8 von _66740_Bauer

Hallo,

geht aus meiner Sicht auch einfacher.

In der Vorhandenen Nummerierung die Funktion Zeile() mit einbauen und nacheinfügen der neuen Zeilen einfach über diese ziehen.

Beispiel:
in A10 steht =Zeile()- 5; also 5
in A11 Steht = Zeile ()- 5 also 6

Nach einfügung einer Zeile Ist A11 A12 somit steht dort als Nummerierung 7 und von A10 wird der Wert nach unten gezogen

Gruß

Helmut

Antwort 9 von pumbaa83

Hi Leute

die Makros funktionieren echt super.

Nur werden leider die unterliegenden Zeilen mit den Formeln nicht beachtet, weil wenn ich zwischen Zeile 4 und 5 eine neue einfüge dann aktualiesiert er mir nicht die "alte" Zeile 5 und die darunter folgenden Zeilen mit den Formel wenn die durch die neue Zeile nach untengeschoben wird.

z.B.

In Zelle A3 ist die Formel =SUMME($G2:$G10)
In Zelle A4 ist die Formel =SUMME($I3:$I5)

wenn ich nun eine Zeile zwischen A3 und A4 einfüge verschiebt er mir die Zelle A4 in Zelle A5 und aktualliesiert aber nicht die Formel und somit steht in der Zelle A5 immer noch =SUMME($I3:$I5)

Aber die ist nun nicht mehr aktuell weil sich auch diese Zellen um eine Zeile nach unten verschoben haben.

Würde diese Aktualisierung auch über ein Makro gehen?
Wenn ja könnte mir da bitte jemand den Code posten?

Wäre euch sehr dankbar.

MfG

Phil

Antwort 10 von CaroS

Hallo ,

ich betrachte mal JöKes Version aus AW7 als den letzten Stand und ändere das Makro so, dass die Formeln der neu eingefügten Zeile und auch die der alten Zeile direkt unter der neu eingefügten angepasst werden.

Sub Neue_Zeile_mit_Formel_3()
Dim r As Range

Set r = Application.InputBox("Bitte eine Zelle oder Zeile auswählen, " & "unterhalb der eine neue Zeile eingefügt werden soll." & Chr(10) & Chr(10) & "In Spalte A wird die Formel aus der alten in die neue Zeile übertragen.", "Neue Zeile einfügen " & _
"unterhalb von:", Selection.Address, Type:=8)

If r.Rows.Count > 1 Then Exit Sub

r.Offset(1, 0).EntireRow.Insert
ActiveSheet.Rows(r.Row).Copy
ActiveSheet.Range("$" & CStr(r.Row + 1) & ":$" & CStr(r.Row + 2)).Select
Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
r.Select
Application.CutCopyMode = False

End Sub


Ob das ausreichend ist, hängt allerdings wesentlich von den Formeln ab. (Für die Summen wahrscheinlich nicht.) Bei manchen Formeln muss man ab einer eingefügten Zeile alle weiteren bis nach unten anpassen, manchmal sogar alle von der ersten bis zur letzen, weil sonst die Bezüge nicht stimmen.

Bei einer Reihe von Funktionen (SUMME, ANZAHL, ANZAHL2, MITTELWERT, MAX, MIN u. a.), z. B. auch bei Deiner "gleitenden" Summe, gibt es eine Besonderheit. Eine neue Zeile, die innerhalb des zu bearbeitenden Bereichs eingefügt wird, wird in den zu bearbeitenden Bereich zusätzlich eingefügt. Der zu bearbeitende Bereich wird dadurch also größer. (Wird die neue Zeile vor dem zu bearbeitenden Bereich eingefügt, werden alle relativen Bezüge nur entsprechend verschoben, wird die neue Zeile erst nach dem zu bearbeitenden Bereich eingefügt, ändern sich die Formeln natürlich gar nicht.)

Bei Deinen Summen ist es so, dass der zu summierende Bereich "weit" oberhalb der einzufügenden Zeile beginnt und erst "weit" unterhalb einzufügenden Zeile endet.
Es würde folgende Situation bei der Addition von 3 Summanden entstehen.
(Ich lasse die absoluten Spaltenbezüge mal weg, da sie keine Rolle spielen.)

Vor dem Einfügen einer neuen Zeile zwischen Zeile 4 und Zeile 5:

A2: =SUMME(I1:I3) - es werden 3 Summanden addiert
A3: =SUMME(I2:I4) - es werden 3 Summanden addiert
A4: =SUMME(I3:I5) - es werden 3 Summanden addiert
- - - - - - - - - - - - - - Hier wird eingefügt- - - - - - - - - - - - - -
A5: =SUMME(I4:I6) - es werden 3 Summanden addiert
A6: =SUMME(I5:I7) - es werden 3 Summanden addiert
A7: =SUMME(I6:I8) - es werden 3 Summanden addiert

Nach dem Einfügen der neuen Zeile 5 zwischen Zeile 4 und Zeile 6:

A2: =SUMME(I1:I3) - es werden 3 Summanden addiert
A3: =SUMME(I2:I4) - es werden 3 Summanden addiert
A4: =SUMME(I3:I6) - es werden 4 Summanden addiert
A5: =SUMME(I4:I7) - es werden 4 Summanden addiert
A6: =SUMME(I5:I8) - es werden 4 Summanden addiert
A7: =SUMME(I6:I8) - es werden 3 Summanden addiert
A8: =SUMME(I7:I9) - es werden 3 Summanden addiert

Du siehst, dass sich die Formel in Zeile 4 angepasst hat und dass der Bereich größer geworden ist. Da die Formel in Zeile 4 außerdem als Vorlage für die neue Zeile 5 und die alte Zeile 6 dient, werden auch dort die Bereiche größer. Ab Zeile 7 sind die Bereiche dagegen gleich groß geblieben und nur um eine Zeile nach unten verschoben.

Hier stellt sich die Frage: Was ist überhaupt richtig, wie soll es denn sein?

Gruß,
CaroS