Zeile einfügen verschiebt nicht alle Formelwerte

Computer: Software & Programmierung Office Software
#1

Salute Profis,

Excel 2010: ich kopiere mittels folgendem VBA-Code eine neue Zeile ein:

Rows(GlZeile:GlZeile).Select
Selection.Copy
Selection.Insert Shift:=xlDown

Aber die enthaltenen Formeln werden nicht vollständig akutalisiert. Wenn ich vor Zeile 13 eine Zeile einfüge (GlZeile = 13), passiert folgendes:

Zeile: Vor dem Einfügen: Nach dem Einfügen: Ich will aber:
 12 =WENN(J12\>0;I11+25;I11) =WENN(J12\>0;I11+25;I11) =WENN(J12\>0;I11+25;I11)
 13 =WENN(J13\>0;I12+25;I12) =WENN(J13\>0;I11+25;I11) =WENN(J13\>0;I12+25;I12)
 14 =WENN(J14\>0;I13+25;I13) =WENN(J14\>0;I12+25;I12) =WENN(J14\>0;I13+25;I13)
 15 =WENN(J15\>0;I14+25;I14) =WENN(J15\>0;I14+25;I14) =WENN(J15\>0;I14+25;I14)

Warum ist das so? Wie kann ich dem abhelfen?

Vielen Dank für Eure Unterstützung
-Rob.

#2

Hallo Rob,

überseh ich irgendwas, wenn ja, was?, jedenfalls sehe ich keinen Unterschied zwischen dem was du willst und dem was geschieht.

Wie wärs, du bastelst eine winzige beispielmappe mit zwei Blättern. In einem wie es sein sollte nach Zeile einfügen im anderen wie es ist nach dem makro und lädst die Mappe hoch.

Gruß
Reinhard

#3

Hallo,

=WENN(J12>0;INDEX($I:blush:I;ZEILE()-1)+25;INDEX($I:blush:I;ZEILE()-1))

Falls das J am Anfang auch Probleme macht, dann ebenfalls in den INDEX() packen.

und noch für unsere älteren Zuschauer…:smile:))
vor =WENN(J13>0;I12+25;I12)
nach =WENN(J13>0; I11 +25; I11 )
soll aber =WENN(J13>0; I12 +25; I12 )

VG René :wink:

#4

Nicht schlecht Herr Specht, aber
Hallo René,

und noch für unsere älteren Zuschauer…:smile:))
…

okay, okay, hab die Tabelle quer verglichen anstatt längs, na gut hab halt den 50/50 Joker vergeigt :smile:

Jetzt mal kurz zu deiner Lösung:

=WENN(J12>0;INDEX($I:blush:I;ZEILE()-1)+25;INDEX($I:blush:I;ZEILE()-1))

Was bitteschön hat denn der Anfrager davon? Er/Sie sucht ja wohl eine Vba-Lösung.

„Excel 2010: ich kopiere mittels folgendem
VBA-Code eine neue Zeile ein:
Rows(GlZeile:GlZeile).Select
Selection.Copy
Selection.Insert Shift:=xlDown“

So wie der Code aussieht ist er/sie nicht fit in Vba, also sehr fraglich ob die Excelformel-Lösung in Vba eigenständig umsetzbar ist wenn überhaupt so gewünscht.

Ich kenne XL 2010 nicht, bei mir XL2000 läuft der Code übrigens nur so:

Sub tt()
Dim GlZeile As Long
GlZeile = 13
Rows(GlZeile & „:“ & GlZeile).Select
Selection.Copy
Selection.Insert Shift:=xlDown
End Sub

Hast du das mit deinen jungen Augen etwa alles übersehen?
*kicher* Ich find es steht 1:1 :smile:

Gruß
Reinhard

#5

Hallo Reinhard,

dass ist aber unfähr. Mit irgendwelchem Text ein 1:1 rausholen wollen!

Sub tt()
Dim GlZeile As Long
GlZeile = 13
Rows(GlZeile& „:“ &GlZeile).Select
Selection.Copy
Selection.Insert Shift:=xlDown
End Sub

Was soll dass sein? Wozu ist dass gut? Was steht dort?
Ich hab doch keine Ahnung von VBA.:frowning:((
(ist dafür auch kein Platz mehr da, in meinem Hirn!)

Somit werde ich aus Unwissenheit, dem 1:1 wohl kleinbei geben müssen!:smile:

VG René

#6

Aber die enthaltenen Formeln werden nicht vollständig
akutalisiert. Wenn ich vor Zeile 13 eine Zeile einfüge
(GlZeile = 13), passiert folgendes:

Zeile: Vor dem Einfügen: Nach dem Einfügen:
Ich will aber:
12 =WENN(J12>0;I11+25;I11) =WENN(J12>0;I11+25;I11)
=WENN(J12>0;I11+25;I11)
13 =WENN(J13>0;I12+25;I12) =WENN(J13>0;I11+25;I11)
=WENN(J13>0;I12+25;I12)
14 =WENN(J14>0;I13+25;I13) =WENN(J14>0;I12+25;I12)

Hallo Rob,

nachfolgende Lösung geriet mir eweng groß, aber erstmal egal, ist es dann so wie du willst?

Gruß
Reinhard

Option Explicit

Sub Einfuegen()
Dim GlZeile As Long, Zelle As Range, Spa As Long
On Error GoTo hell
Application.ScreenUpdating = False
GlZeile = 13
Rows(GlZeile).Insert
For Each Zelle In Rows(GlZeile + 1).SpecialCells(xlCellTypeFormulas)
 Spa = Zelle.Column
 Zelle.Offset(-1).Formula = Zelle.Formula
 Zelle.Offset(-1).AutoFill Destination:=Range(Cells(GlZeile, Spa), Cells(Cells(Rows.Count, Spa).End(xlUp).Row, Spa)), Type:=xlFillCopy
Next Zelle
hell:
Application.ScreenUpdating = True
If Err.Number 0 Then MsgBox Err.Number & vbCr & Err.Description
End Sub
#7

Hallo René,

dass ist aber unfähr. Mit irgendwelchem Text ein 1:1 rausholen
wollen!

soso, unfähr, also 2:1 für mich *grien*
Frag mal einen Älteren warum unfähr für mich 2:1 bedeutet *fiesgrins*

Was soll dass sein? Wozu ist dass gut? Was steht dort?
Ich hab doch keine Ahnung von VBA.:frowning:((
(ist dafür auch kein Platz mehr da, in meinem Hirn!)

Somit werde ich aus Unwissenheit, dem 1:1 wohl kleinbei geben
müssen!:smile:

Macht doch nix, ich mache soviele Fehler, da kommst du noch sehr oft zum Zuge um das zu verbessern :smile:)

Übrigens, ich habe jetzt deine Formel noch gar nicht angeschaut/geprüft.
Vielleicht ist sie ja genau die Lösung auch für Vba-Einsatz.
Ist ja letztlich egal was Vba da macht. Wenn deine oder eine Formel die halt anders strukturiert ist als die vorhandene es erreicht daß nach Vba das

Aaargs, Gekas der penner trifft nur die Latte :frowning:(

Endergebnis, also die enstandenen neuen Formeln dem entsprechen was gefordert ist so ist es doch DIE Lösung.

Mit Gekas oder wie sich die Niete nennt meine ich Eintracht Frankfurt. Ich spiele ja nie diese Fußballwetten. Aber jetzt am letzten Spieltag habe ich dann doch mal geschaut was da für Quoten geboten werden.

Hätte ich 10€ auf das Halbzeitergebnis D-F gesetzt und es stünde zur Halbzeit 1:2 hätte ich dafür 420€ gekriegt *träum*
Okay, Realität ist etwas anderes, die 10 € wären jetzt weg weil es 0:0 steht.

Und ich habe insgesamt zur Eintracht eine zwiespältige Einstellung, einerseits, wer sone Scheiße in der Rückrunde spielt, ganz klar raus aus der Bundesliga, sammeln, neu aufbauen.
Andererseits, eine Minimalchance gibt es ja noch…

Ups, kam wohl leicht weg von Excel aber liebe MODs trotzdem ist das natürlich kein Grund wegen OT zu löschen *find*

@René, vielleicht konntest du meinen Gedankensprüngen nicht ganz folgen weil du eher FC facebook oder FC Skype usw. kennst.
3:1 *hihi*

Zurück zur Anfrage, ich warte da erstmal ab ob und mit was der Anfrager sich meldet.

Gruß
Reinhard

#8

Habe das Makro ausprobiert
Salve Reinhard,

nachfolgende Lösung geriet mir eweng groß, aber erstmal egal,
ist es dann so wie du willst?

Option Explicit

Sub Einfuegen()
Dim GlZeile As Long, Zelle As Range, Spa As Long
On Error GoTo hell
Application.ScreenUpdating = False
GlZeile = 13
Rows(GlZeile).Insert

For Each Zelle In Rows(GlZeile +
1).SpecialCells(xlCellTypeFormulas)
Spa = Zelle.Column
Zelle.Offset(-1).Formula = Zelle.Formula
Zelle.Offset(-1).AutoFill Destination:=Range(Cells(GlZeile, Spa), Cells(Cells(Rows.Count, Spa).End(xlUp).Row, Spa)), Type:=xlFillCopy
Next Zelle

hell:
Application.ScreenUpdating = True
If Err.Number 0 Then MsgBox Err.Number & vbCr &
Err.Description
End Sub

Viiiieelen Dank für Deine Bemühungen :o) Du hast es mit Deiner Lösung schon richtig erraten: Es sollen zwar sämtliche Formatierungen, nicht aber statische Zellinhalte mitkopiert werden (so wie ich das sehe selektiert die Methode .Formula das richtige heraus). Dieses Makro benötigt aber enorm viel Zeit (ca. 30 Sekunden, Spalten bis P teilweise belegt, 2 x 3 GHz Rechner). Außerdem werden dadurh die Zeilennummern in den Formeln der nach unten verschobenen Zeilen einen Zähler heraufgesetzt…

Ich habe das Problem der Nicht-Verschobenheiten mittlerweile auch mit einer mir stabiler vorkommenden Variante gelöst, indem ich sämtliche Zellbezüge außerhalb der eigenen Zellreihe mit INDIREKT(…) ausgeführt habe.

Wie könnten wir das Makro auf einen normalen Nur-Formeln-Kopiervorgang reduzieren? Vielleicht läuft’s dann schneller?

Nochmals beste Grüße vom Dankbaren
-Rob.

P. S.: Mail mir doch mal eine PayPal- oder Bankverbindung, ich würde mich wirklich gerne erkenntlich zeigen, wenn das Dingen hier klappt.

#9

Hallo Rob,

entweder spinnt mein Browser oder ich hab beim Posten ein vergessen oder sowas, jedenfalls ist mein Posting jetzt im Codebereich unleserlich. Bei dir sieht es wiederum so aus wie es sein sollte. Hmmh *grübel*

Dieses Makro benötigt aber enorm viel
Zeit (ca. 30 Sekunden, Spalten bis P teilweise belegt, 2 x 3
GHz Rechner).

30 sec alleinstehend besagen nix, gibt Vba-Code der läuft mehrere Tage.
Andrerseits sehe ich da nix was da den Code länger als 1-max2 sec zu laufen.
Gut, es gibt Arrayformeln, wenn die umkopiert werden und anfangen neu zu berechnen dann dauert es so lang und noch länger.

Kannst du mal bitte deine Originalmappe hochladen, intime daten kannste ja anonymisieren, Rest kann ja bleiben bzw. muß auch bleiben, ich brauche ja die gleichgroße Datenmenge um überhaupt testen zu können bezüglich der Zeit.

Anonymisieren auch einer großen Tabelle ist Sache von wenigen Minuten, frag nach wie man das schnell machen kann.

Außerdem werden dadurh die Zeilennummern in den
Formeln der nach unten verschobenen Zeilen einen Zähler
heraufgesetzt…

Auch dafür wäre eine Mappe hilfreich, wo du z.B. in Blatt1 deine Rohdaten hast und in Blatt2 dann diese Daten nach dem makro und mir dann genau sagen kannst auf was ich schauen muß wenn ich da den hochgezählten Zähler suchen muß.

Ich habe das Problem der Nicht-Verschobenheiten mittlerweile
auch mit einer mir stabiler vorkommenden Variante gelöst,
indem ich sämtliche Zellbezüge außerhalb der eigenen Zellreihe
mit INDIREKT(…) ausgeführt habe.

Wenn das DIE Lösung ist ist es ja okay, wenn das aber nur ein notgedrungener workaround ist der jedesmal aufs neue gemacht werden muß ist das schlecht.

Wie könnten wir das Makro auf einen normalen
Nur-Formeln-Kopiervorgang reduzieren? Vielleicht läuft’s dann
schneller?

Wenn ich die Mappe habe kann ich mehr sagen/vermuten.

Nochmals beste Grüße vom Dankbaren

de nada.

Gruß
Reinhard

#10

Noch ein neuer Versuch
Salute nochmal,

letztlich möchte in nun nur eine neue Zeile einfügen, unter Berücksichtigung von zwei Dingen

  • Die neue Zeile muß wie die darunterliegende formatiert sein, damit man auch über die oberste Zeile eines Abschnittes eine neue Zeile gleicher Art einfügen kann. Der Standardbefehl .Insert orientiert sich leider nur an der darüberliegenden Zeile.

  • Es müssen und dürfen nur alle Formeln in transponierter Form kopiert werden.

Bis jetzt behelfe ich mir mit

GlZeile = Selection.Row

Range(Rows(GlZeile), Rows(GlZeile)).Copy
Range(Rows(GlZeile), Rows(GlZeile)).Insert Shift:=xlDown
Range(Rows(GlZeile), Rows(GlZeile)).SpecialCells(xlCellTypeConstants).ClearConstants

Ist das guter, stabiler Code? Oder handele ich mir damit irgendwelche Probleme ein?

Viele Grüße
-Rob.

#11

Salute Reinhard,

Kannst du mal bitte deine Originalmappe hochladen, intime
daten kannste ja anonymisieren, Rest kann ja bleiben bzw. muß
auch bleiben, ich brauche ja die gleichgroße Datenmenge um
überhaupt testen zu können bezüglich der Zeit.

Da haben sich unsere Postings wohl überschnitten. Ich mache gleich mal eine Mappe zureckt.

Bestliche Grüße :o)
-Rob.