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.
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))
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.
Hallo Edgar,
darauf muss man erst einmal kommen. Klasse
Heinz
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
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
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
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
Hallo Heinz,
z.B. einfach mal eine Division durch 0 ...