Einfügen einer Zeile soll Formeln kopieren

[Heinz Ulm]

Hallo VBA-Freunde,

ich stehe wieder mal auf dem Schlauch.

Ich möchte gerne in eine Tabelle ab Zeile 17bis Zeile 120 eine komplette Zeile einfügen. Z.B. eine neue Zeile 20.


Nach dem Einfügen sollen die Formeln In A17 bis E17 komplett bis Zeile 120 herunterkopiert werden.
Dabei komme ich mit der Funktion 

Private Sub Worksheet_Change(ByVal Target As Range)

Das ist mein bisheriger Code. Wo liegt denn mein Fehler?

Private Sub Worksheet_Change(ByVal Target As Range)
'Ueberwachung der Zelle G4 f?r das kopieren der Formeln in A:E; H und J:K
' Feststellen in welcher Zeile "Ende" stand bzw. jetzt steht
    Alt = Worksheets("Steuerungswerte").Range("K3").Value
    Neu = Worksheets("Steuerungswerte").Range("K4").Value
' Wenn keine ?nderung erfolgte oder keine Zeile gel?scht wurde Code verlassen
    TRow = Target.Row
'    MsgBox (TRow)
    If Range("G4") = "OK" Then ' Bei OK den Code verlassen
'            MsgBox ("Keine Aktion notwendig")
        Exit Sub
    End If
' Kopieren der einzelnen Formelspalten
'    MsgBox ("Kopieren")
    Range("A" & TRow - 1 & ":E" & TRow - 1).Copy
    Range("A" & TRow).Select
    ActiveSheet.Paste

'    Range("A17:E17").Select
'    Selection.AutoFill Destination:=Range("A17:E" & Neu), Type:=xlFillDefault
End Sub
Der Bereich F17:F116 hat auch noch ein Makro für einen Doppelklick.

[BoskoBiati]

Hallo Heinz,


in Deiner Datei ist nur ein Makro, und das ist keiner Tabelle zugeordnet. Somit ist unklar, um welche Tabelle es geht.
Das Makro im Text funktioniert nur in einer Tabelle.
Warum willst Du die Formeln bis Zeile 120 ausfüllen?
Die Formel in J kann man deutlich verschlanken:

Code:

=WENN(I16="";"";MAX($J$1:J15)+(CODE(I16)=63))

[Heinz Ulm]

Hallo Edgar,

Danke für deine Antwort,

hier ein paar Erlkärungen was ich tun will. Hinter der Tabelle "Eingabe Feld" sind 2 Makros.
1. Makro: Eintrag eines X in eine Zelle des Bereiches F17 : F120. Das klappt einwandfrei.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)


So jetzt zu meinem Problem, dem 2. Makro hinter dieser Tabelle

Ein paar User sind berechtigt die Grundlagentabelle (passwortgeschützt) zu ändern, z.B. neue Zeilen einzufügen.

Ein paar Bereiche habe ich dann ausgeblendet: A:F und J:K. In diesen Bereichen sind Formeln, welche zur Steuerung benötigt werden. Wird nun eine Zeile eingefügt fehlen diese Formeln, deshalb müssten sie neu herunterkopiert werden. Ich denke aber, das wird öfters vergessen! Deshalb möchte ich nach jedem Einfügen einer Zeile die Formeln per VBA einfach neu kopieren.

Mir fehlt eigentlich nur das auslösende Ereignis. Worksheet Change bekam ich nicht hin, Worksheet Calculate führt zur Endlosschleife.

Ich habe schon per Formel die alte Länge und dann die neue Länge. Und dachte dann wenn in der G4 "Kopieren" steht, dann könnte ich damit den Code auslösen.
Mit einer Wenn und Function ging es aber auch nicht.

Mal sehen was noch kommt.

[Heinz Ulm]

Hallo Edgar,

darauf muss man erst einmal kommen. Klasse

Heinz

[Steffl]

Hallo Heinz,

das kopieren der Spalten A bis E löst ein weiteres Mal das Ereignis aus. Du musst es ausschalten. Versuche es mal so

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
'Ueberwachung der Zelle G4 für das kopieren der Formeln in A:E; H und J:K

' Feststellen in welcher Zeile "Ende" stand bzw. jetzt steht

    Alt = Worksheets("Steuerungswerte").Range("K3").Value
    Neu = Worksheets("Steuerungswerte").Range("K4").Value

' Wenn keine Änderung erfolgte oder keine Zeile gelöscht wurde Code verlassen
    
    TRow = Target.Row
'    MsgBox (TRow)
    
    If Range("G4") = "OK" Then ' Bei OK den Code verlassen
'            MsgBox ("Keine Aktion notwendig")
        Exit Sub
    End If

' Kopieren der einzelnen Formelspalten
'    MsgBox ("Kopieren")
    Application.EnableEvents = False
    Range("A" & TRow - 1 & ":E" & TRow - 1).Copy Range("A" & TRow)
    Application.EnableEvents = True
    
'    Range("A17:E17").Select
'    Selection.AutoFill Destination:=Range("A17:E" & Neu), Type:=xlFillDefault

End Sub

[Heinz Ulm]

Hallo Stefan,

Danke.

In Application.EnableEvents = False lag mein Fehler.
Mit dem Ausschalten der Events lief das Makro dann wie ich es wollte.

Eine Frage noch:

Was würde passieren, wenn Excel während des Makros abschmiert?
Dann werden ja die Events nicht mehr auf true gesetzt.

Wäre beim Öffnen der Mappe ein 
Application.EnableEvents = True sinnvoll?

Gruß Heinz

[schauan]

Hallo Heinz,

wenn Excel abschmiert, ist alles gut. Beim Neustart von Excel sind die Events wieder da.

Problematischer ist, wenn die Debuggen-Meldung kommt und der user drückt auf Beenden. Dann bleiben sie weg bis sie mal wieder einer einstellt oder eben Excel neu gestartet wird. Das könntest Du an verschiedenen Stellen wie Blattwechsel (WorksheetActivate, WorkbookActivate, -Deactivate, -BeforeClose usw) vorantreiben


Alternativ könntest Du das über eine individuelle Fehlerbehandlung regeln. Im Prinzip so:

Sub ABC()
On Error Goto ErrorHandler
Application.EnableEvents=False
..
Dein Code
..
ErrorHandler:
If Err<>0 then
MsgBox "Fehler: " & err.number & vblf & err.description
End If
Application.EnableEvents=True
End Sub

[Heinz Ulm]

Hallo Andre,

vielen Dank, ich habe mal den Errorhandler so eingebaut.

Weist du vielleicht, wie man in dem Code einen Fehler auslösen könnte, damit der Errorhandler anspricht?

Viele Grüße
Heinz

[schauan]

Hallo Heinz,

z.B. einfach mal eine Division durch 0 ...