Option
Explicit
Private
zWsh
As
Worksheet
Private
oWsh
As
Worksheet
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
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
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
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
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