Hallo Hajo
anbei der Code.
Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
Dim tRow As Long, n As Integer
Dim arrSheets As Variant
'Tabellennamen - anpassen!
arrSheets = Array("Jan", "Febr", "März", "Apr", "Mai", "Juni", "Juli", _
"Aug", "Sept", "Okt", "Nov", "Dez")
For n = 0 To 11
If sh.Name = arrSheets(n) Then
If Target.Column = 5 And Target.Row > 12 And Target.Count = 1 Then
On Error GoTo ERRORHANDLER
Application.EnableEvents = False
If LCase(Target) Like "*ab*" Then
Target = "Abrechnung "
tRow = Target.Row - 1
Target.Offset(0, 1).Formula = _
"=SUM(" & Range(Cells(14, 6), Cells(tRow, 6)).Address & ")+" & _
"(SUM(" & Range(Cells(14, 7), Cells(tRow, 7)).Address & ")-" & _
"MOD(SUM(" & Range(Cells(14, 7), Cells(tRow, 7)).Address & "), 100))/100"
Target.Offset(0, 2).Formula = _
"=MOD(SUM(" & Range(Cells(14, 7), Cells(tRow, 7)).Address & "), 100)"
Target.Offset(0, 3).Formula = _
"=SUM(" & Range(Cells(14, 8), Cells(tRow, 8)).Address & ")+" & _
"(SUM(" & Range(Cells(14, 9), Cells(tRow, 9)).Address & ")-" & _
"MOD(SUM(" & Range(Cells(14, 9), Cells(tRow, 9)).Address & "), 100))/100"
Target.Offset(0, 4).Formula = _
"=MOD(SUM(" & Range(Cells(14, 9), Cells(tRow, 9)).Address & "), 100)"
Target.Offset(0, 5).Formula = _
"=SUM(" & Range(Cells(14, 10), Cells(tRow, 10)).Address & ")+" & _
"(SUM(" & Range(Cells(14, 11), Cells(tRow, 11)).Address & ")-" & _
"MOD(SUM(" & Range(Cells(14, 11), Cells(tRow, 11)).Address & "), 100))/100"
Target.Offset(0, 6).Formula = _
"=MOD(SUM(" & Range(Cells(14, 11), Cells(tRow, 11)).Address & "), 100)"
Target.Offset(0, 7).Formula = _
"=SUM(" & Range(Cells(14, 12), Cells(tRow, 12)).Address & ")+" & _
"(SUM(" & Range(Cells(14, 13), Cells(tRow, 13)).Address & ")-" & _
"MOD(SUM(" & Range(Cells(14, 13), Cells(tRow, 13)).Address & "), 100))/100"
Target.Offset(0, 8).Formula = _
"=MOD(SUM(" & Range(Cells(14, 13), Cells(tRow, 13)).Address & "), 100)"
'formel für N bis O
Target.Offset(0, 9).Formula = _
"=SUM(" & Range(Cells(14, 14), Cells(tRow, 14)).Address & ")+" & _
"(SUM(" & Range(Cells(14, 15), Cells(tRow, 15)).Address & ")-" & _
"MOD(SUM(" & Range(Cells(14, 15), Cells(tRow, 15)).Address & "), 100))/100"
Target.Offset(0, 10).Formula = _
"=MOD(SUM(" & Range(Cells(14, 15), Cells(tRow, 15)).Address & "), 100)"
'formel für P bis Q
Target.Offset(0, 11).Formula = _
"=SUM(" & Range(Cells(14, 16), Cells(tRow, 16)).Address & ")+" & _
"(SUM(" & Range(Cells(14, 17), Cells(tRow, 17)).Address & ")-" & _
"MOD(SUM(" & Range(Cells(14, 17), Cells(tRow, 17)).Address & "), 100))/100"
Target.Offset(0, 12).Formula = _
"=MOD(SUM(" & Range(Cells(14, 17), Cells(tRow, 17)).Address & "), 100)"
End If
makeFrame Target.Row, sh
End If
Exit For
End If
Next
ERRORHANDLER:
Application.EnableEvents = True
End Sub
Oder doch lieber die abgespeckte Datei ins Internet stellen?
Gruß Peter