Hallo Alex,
ad Tabelle2 :
"01.01.2002 -> 30.06.2002" verkompliziert die Sache
bau die Tabelle2 zum Test einmal um
von bis Basis Verbrauchergeschäft Handelsgeschäft
01.01.2002 30.06.2002 2,57% 7,57% 10,57%
ff.
benenne Tabelle2 in "Zinssätze" um
ad Tabelle1 als Hilfe zur Selbsthilfe :
beigefügten Code in ein VBA Modul
die Sub ZinsenVerzug macht erstmals eine Demo-Tabelle "Verzugszinsen"
ad "eine Formel die anhand von" :
ist eine benutzerdefinierte Funktion (wird in der Demo in Zelle I9 erzeugt)
kann auch woswasiwohin kopiert werden, d.h. auch mehrere Zeilen möglich
ad "muss unterschieden werden, welcher Zinssatz"
vgl. B5, wechselt hier der Wert wird neu berechnet
ad " Beginn und Ende der Verzinssung die richten Zinssätze aus der Tabelle 2 raussucht und"
erledigt die benutzerdefinierte Funktion (heute schlicht und ergreifend in Schleifen)
PS: In Mathe hat ich nich studiert, daher selbst austesten ;-)
Option Explicit
Private zWsh As Worksheet 'Zinstabelle mit Namen "Zinssätze" !!
Private oWsh As Worksheet 'zur Visualisierung ein eigenes Arbeitsblatt
Sub ZinsenVerzug()
Set zWsh = Sheets("Zinssätze")
On Error Resume Next
Set oWsh = Sheets("Verzugszinsen")
On Error GoTo 0
If oWsh Is Nothing Then
Sheets.Add After:=Sheets(Sheets.Count)
Set oWsh = ActiveSheet
oWsh.Name = "Verzugszinsen"
End If
oWsh.Select
Cells.Clear
oWsh.ClearCircles
[A1].Formula = "Kundenname"
[B4].Formula = "Basiszinssatz +"
With [B5].Validation
.Delete
.Add Type:=3, AlertStyle:=1, Operator:=1, _
Formula1:="=Zinssätze!$D$1:$E$1"
End With
[B5].Formula = "=Zinssätze!$D$1"
[A7].Formula = "Kundennummer"
[B7].Formula = "Vetragaskonto"
[C7].Formula = "RG-NR"
[D7].Formula = "RG Datum"
[E7].Formula = " RG Betrag"
[F7].Formula = "Zinsen ab RG-Betrag + 31"
[G7].Formula = "verzinsen bis zum"
[H7].Formula = "Zinsstage"
[I7].Formula = "Zu zahlen"
[E9].Value = 5000
[D9].Value = CDate("23.05.2010")
[F9].FormulaR1C1 = "=RC[-2]+31"
[G9].Value = CDate("02.03.2014")
[H9].FormulaR1C1 = "=RC[-1]-RC[-2]"
Range(Columns(1), Columns(9)).AutoFit
[I9].Formula = "=BerEingabe($B$5,ROW())"
End Sub
Private Function BerEingabe(Zellbezug, ZielZeile)
Dim Vorgabe As String
Dim InZeile As Long
Dim ZinsBeg, ZinsEnd
Dim x As Long, y As Long
Dim Start As Long, Ende As Long
Dim Kapital As Double, ActZins As Double
Dim zTage As Long
Dim Zinsspalte As Long
Vorgabe = Zellbezug
InZeile = ZielZeile
ZinsBeg = Cells(ZielZeile, 6).Value
ZinsEnd = Cells(ZielZeile, 7).Value
Kapital = Cells(ZielZeile, 5).Value
Zinsspalte = Range(Sheets("Zinssätze").Cells(1, 1), _
Sheets("Zinssätze").Cells(1, 5)).Find([B5].Value).Column
'Zinstabelle mit Namen "Zinssätze" !!
y = Sheets("Zinssätze").Cells(Rows.Count, 1).End(xlUp).Row
For x = 2 To y
If ZinsBeg >= Sheets("Zinssätze").Cells(x, 1).Value And _
ZinsBeg <= Sheets("Zinssätze").Cells(x, 2).Value Then _
Exit For
Next x
Start = x
For x = Start To y
If ZinsEnd >= Sheets("Zinssätze").Cells(x, 1).Value And _
ZinsEnd <= Sheets("Zinssätze").Cells(x, 2).Value Then _
Exit For
Next x
Ende = x
'Start
ActZins = ActZins + _
Sheets("Zinssätze").Cells(Start, Zinsspalte).Value / _
TageJahr(Sheets("Zinssätze").Cells(Start, 1).Value) * _
(Sheets("Zinssätze").Cells(Start, 2).Value - ZinsBeg) * Kapital
'If ActZins = 0 Then Start = Start - 1 'Sonderfall
'Ende
ActZins = ActZins + _
Sheets("Zinssätze").Cells(Ende, Zinsspalte).Value / _
TageJahr(Sheets("Zinssätze").Cells(Ende, 2).Value) * _
(ZinsEnd - Sheets("Zinssätze").Cells(Ende, 1).Value) * Kapital
'dazwischen
For x = Start + 1 To Ende - 1
ActZins = ActZins + _
Sheets("Zinssätze").Cells(x, Zinsspalte).Value / _
TageJahr(Sheets("Zinssätze").Cells(x, 1).Value) * _
(Sheets("Zinssätze").Cells(x, 2).Value - _
Sheets("Zinssätze").Cells(x, 1).Value) * Kapital
Next x
BerEingabe = Round(ActZins, 2)
End Function
Private Function TageJahr(Datum) As Long
Dim Jahr As Long
Jahr = Year(Datum)
TageJahr = 365
If (Jahr Mod 4) = 0 And (Jahr Mod 100) <> 0 Or (Jahr Mod 400) = 0 Then _
TageJahr = 366
End Function
|