Thema: Summe auf Jahre aufteilen | Herbers Excel-Forum

Hallo,
der nachstehende Code bewirkt, dass eine Summe in Textbox9 ggfs. über mehrere Jahre aufgeteilt wird. Also, z.B. v. 01.11.09-31.01.10 somit 2 Monate in 2009 anteilig und 1 Monat in 2010. Könnte der Code evtl. so abgeändert werden, dass die Aufteilung ins nächste Jahr nicht erst am 01.01. des Jahres vorgenommen wird, sondern schon ab 01.12. des Jahres? Der Dezember somit schon dem neuen Jahr zugeschlagen wird? - Schon jetzt recht herzlichen Dank für die Rückmeldungen.
Gruß - Wolfgang

Private Sub Jahresaufteilung()
Dim i As Integer
Dim vonDatum As Date, bisDatum As Date
Dim SchnittTag As Double
With Application.WorksheetFunction
If IsDate(TextBox5) And IsDate(TextBox6) Then
TextBox25 = .Round((.Max(CDate(TextBox5), CDate(TextBox6)) - .Min(CDate(TextBox5),   _
_
CDate(TextBox6)) + 1) / 30.4, 1)
SchnittTag = CDbl(TextBox9) / (CDate(TextBox6) - CDate(TextBox5) + 1)
For i = 0 To Year(CDate(TextBox6)) - Year(CDate(TextBox5))
If i = 0 Then
vonDatum = CDate(TextBox5)
bisDatum = .Min(DateSerial(Year(CDate(TextBox5)), 12, 31), CDate(TextBox6))
Else
vonDatum = .Min(DateSerial(Year(CDate(TextBox5)) + i, 1, 1), CDate(TextBox6))
bisDatum = .Min(DateSerial(Year(CDate(TextBox5)) + i, 12, 31), CDate(TextBox6))
If i > 3 Then bisDatum = CDate(TextBox6)
End If
Me("TextBox" & 13 + i) = .Round(SchnittTag * (CDbl(bisDatum) - CDbl(vonDatum) + 1), _
_
2)
'Me("TextBox" & 13 + i) = SchnittTag * (CDbl(bisDatum) - CDbl(vonDatum) + 1)
If i < 3 Then Me("Label" & 24 + i).Caption = "Jahr " & Year(CDate(TextBox5)) + i
If i > 2 Then Exit For
Next i
Else
TextBox25 = ""
End If
End With
End Sub

Hallo,
mach aus der Zeile
vonDatum = .Min(DateSerial(Year(CDate(TextBox5)) + i , 12, 1), CDate(TextBox6))
diese
vonDatum = .Min(DateSerial(Year(CDate(TextBox5)) + i -1, 12, 1), CDate(TextBox6))
Habe ich aber nicht getestet.
Gruß Tino

Hallo,
ich denke es müssten folgende Zeilen geändert werden: (auch ungetestet)

bisDatum = .Min(DateSerial(Year(CDate(TextBox5)), 11, 30), CDate(TextBox6))
Else
vonDatum = .Min(DateSerial(Year(CDate(TextBox5)) , 12, 1), CDate(TextBox6))
bisDatum = .Min(DateSerial(Year(CDate(TextBox5)) + i, 11, 30), CDate(TextBox6))

Gruß
Bosko

Hallo Tino,
Hallo Bosko,
Danke zunächst für Euere Rückmeldungen.
Ich habe Eure Ergänzungen/Änderungen soweit aufgegriffen. Bei Deiner Ergänzung, Tino, ergibt sich keine Veränderung (habe dabei das vonDatum in bisDatum geändert);
Bei Deinen Ergänzungen, Bosko, rechnet der Code sauber bis zum 30.11. - habe im Beispiel 1000 Euro für die Zeit vom 01.11.09-15.12.09 aufteilen lassen. Im Textfenster für das Jahr 2009 erscheinen auch 666,67 nur im nächsten Textfenster erscheint nun der Rest (ab 01.12.09) nicht mehr. Hättest Du da evt. noch eine Idee, was ich da verändern müsste? - Danke schon jetzt für die Rückmeldungen.

Hallo,

"habe dabei das vonDatum in bisDatum geändert"

, Du wolltest doch einen Monat vorher anfangen.
DateSerial(Year(CDate(TextBox5)) + i -1, 12, 1) macht aus z. Bsp. 01.01.2010 den 01.12.2009?
Warum Du nun vonDatum in bisDatum änderst verstehe ich nicht.
Gruß Tino

Hallo Tino,
danke für Deine erneute Rückmeldung; ich hatte zunächst auch vonDatum stehen lassen. Das funktionierte aber nicht -Debugger zeigt beim 01.11.09 immer noch den 01.11.09, da hatte ich mir gedacht, dass es evtl. ein Versehen von Dir war. Was mache ich denn evtl. verkehrt? - Der Code von Bosko läuft soweit, aber in den weiteren Textfenstern (Jahren) wird nichts mehr angezeigt. Hättest Du evtl. noch Ideen, was ich in dem einen oder anderen Code ändern müsste?
Gruß - Wolfgang

Hallo,
in meiner Formel habe ich das i rausgeworfen, was natürlich falsch war.
Ein Versuch noch:

bisDatum = .Min(DateSerial(Year(CDate(TextBox5)), 11, 30), CDate(TextBox6))
Else
vonDatum = .Min(DateSerial(Year(CDate(TextBox5)) i-1, 12, 1), CDate(TextBox6))
bisDatum = .Min(DateSerial(Year(CDate(TextBox5)) + i, 11, 30), CDate(TextBox6))

Gruß
Bosko

Hallo Bosko,
habe Deine Änderungen eingebaut. - Es erscheint Fehlermeldung "erwartet Listentrennzeichen oder );
vonDatum = .Min(DateSerial(Year(CDate(TextBox5)) i - 1, 12, 1), CDate(TextBox6))
Was muß ich noch einbauen?
Danke und Gruß
Wolfgang

(ohne weiteren Text)

Hallo Erich, Tino und Bosko,
ich habe noch länger getestet und versucht. Egal, welche Variation ich nehme - die Summe wird immer nur für das eine Jahr z.B. 01.11.09-15.12.09 -Anteil erscheint im Textfenster 2009-; erst wenn ich als Enddatum z.B. 01.01.10 eingebe, wird auch 2010 angezeigt, dann auch durchaus mit der richtigen Verteilung. Was kann ich noch ändern, damit auch schon ab 01.12.** eines jeden Jahres die Summe im Folgejahrtextfenster angezeigt wird. Ich habe unten den geänderten Code eingefügt. Danke schon jetzt wieder für die Rückmeldungen.
Gruß - Wolfgang

Private Sub verteilen()
Dim i As Integer
Dim vonDatum As Date, bisDatum As Date
Dim SchnittTag As Double
With Application.WorksheetFunction
If IsDate(TextBox5) And IsDate(TextBox6) Then
TextBox25 = .Round((.Max(CDate(TextBox5), CDate(TextBox6)) - .Min(CDate(TextBox5),  _
CDate(TextBox6)) + 1) / 30.4, 1)
SchnittTag = CDbl(TextBox10) / (CDate(TextBox6) - CDate(TextBox5) + 1)
For i = 0 To Year(CDate(TextBox6)) - Year(CDate(TextBox5))
If i = 0 Then
vonDatum = CDate(TextBox5)
'bisDatum = .Min(DateSerial(Year(CDate(TextBox5)), 12, 31), CDate(TextBox6)) ' _
ursprünglich
bisDatum = .Min(DateSerial(Year(CDate(TextBox5)), 11, 30), CDate(TextBox6)) 'von  _
Bosko
Else
'vonDatum = .Min(DateSerial(Year(CDate(TextBox5)) + i - 1, 12, 1), CDate(TextBox6)) _
'von Tino
'vonDatum = .Min(DateSerial(Year(CDate(TextBox5)) + i, 1, 1), CDate(TextBox6)) ' _
ursprünglich
'bisDatum = .Min(DateSerial(Year(CDate(TextBox5)) + i, 12, 31), CDate(TextBox6)) ' _
ursprünglich
vonDatum = .Min(DateSerial(Year(CDate(TextBox5)) + i - 1, 12, 1), CDate(TextBox6)) _
'von Bosko
bisDatum = .Min(DateSerial(Year(CDate(TextBox5)) + i, 11, 30), CDate(TextBox6)) ' _
von Bosko
If i > 3 Then bisDatum = CDate(TextBox6)
End If
Me("TextBox" & 13 + i) = .Round(SchnittTag * (CDbl(bisDatum) - CDbl(vonDatum) + 1), _
2)
'Me("TextBox" & 13 + i) = SchnittTag * (CDbl(bisDatum) - CDbl(vonDatum) + 1)
If i < 3 Then Me("Label" & 24 + i).Caption = "Jahr " & Year(CDate(TextBox5)) + i
If i > 2 Then Exit For
Next i
Else
TextBox25 = ""
End If
End With
End Sub

Hallo,
damit ich selbst spielen kann damit.
Gruß Tino

Hi Wolfgang,
obwohl ich mich Tinos Meinung voll und ganz anschließe - ohne Beispielmappe ist wohl kaum zu helfen -,
hier mal eine Trockenübung ohne jeglichen Test, vor allem wegen des Versuchs,
den Code ein wenig übersichtlicher zu gestalten.
Vielleicht hilft ja auch die Idee mit "CDate(TextBox5) + 31".

Private Sub verteilen()
Dim daTB5 As Date, daTB6 As Date, intB5j As Integer
Dim SchnittTag As Double, ii As Integer
Dim datVon As Date, datBis As Date
With Application.WorksheetFunction
If IsDate(TextBox5) And IsDate(TextBox6) Then
daTB5 = CDate(TextBox5) + 31
daTB6 = CDate(TextBox6) + 31
intB5j = Year(daTB5)
TextBox25 = .Round((.Max(daTB5, daTB6) - .Min(daTB5, daTB6) + 1) / 30.4, 1)
SchnittTag = CDbl(TextBox10) / (daTB6 - daTB5 + 1)    ' Wert pro Tag
For ii = 0 To Year(daTB6) - intB5j
Select Case ii
Case 0
datVon = daTB5
datBis = .Min(DateSerial(intB5j, 12, 31), daTB6)
Case Year(daTB6) - intB5j
datVon = .Min(DateSerial(intB5j, 12, 31), daTB6)
datBis = daTB6
Case Else
datVon = DateSerial(intB5j + ii, 12, 31)
datBis = DateSerial(intB5j + ii, 12, 31)
End Select
Me("TextBox" & 13 + ii) = .Round(SchnittTag * (datBis - datVon + 1), 2)
If ii < 3 Then Me("Label" & 24 + ii).Caption = "Jahr " & intB5j + ii
If ii > 2 Then Exit For ' warum das ?
Next ii
Else
TextBox25 = ""
End If
End With
End Sub

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

Hallo Erich und Tino,
sorry, dass ich mich gestern nicht mehr melden konnte; Ich habe anbei einmal eine abgespeckte Beispielsmappe angefügt. Es werden vielleicht nicht alle Codes laufen. Entscheidend ist der Code "entschieden", hierüber wüde ich gerne die Aufteilung nach Jahren erreichen. Ich hatte noch eben Deinen Code, Erich, eingebaut. Der zeigt zwar die Kosten auch für das nächste Jahr an, gehe ich aber über mehrere Jahre, also z.B. bis 2011, dann rechnet er fast alles in 2011 'rein. Danke schon jetzt für Eure Bemühungen und Rückmeldungen.
Herzliche Grüße - Wolfgang
https://www.herber.de/bbs/user/65911.xls

Hi Wolfgang,
wenn man die Mappe mal kompilierbar bekommen (da waren noch ein paar Aufrufe auszukommentieren)
und mit "Option Explicit" vervollständigt hat (da gabe ist tntC statt intC,
eine Variable spalte war nicht deklariert), war es mit dem Beispiel nicht mehr so schwer.
Probier mal:

Private Sub entschieden() '####### zunächst geht es um diesen Code#####
Dim daTB5 As Date, daTB6 As Date, intB5j As Integer
Dim SchnittTag As Double, ii As Integer
Dim datVon As Date, datBis As Date
With Application.WorksheetFunction
If IsDate(TextBox5) And IsDate(TextBox6) Then
daTB5 = CDate(TextBox5) + 31
daTB6 = CDate(TextBox6) + 32
intB5j = Year(daTB5)
TextBox25 = .Round((.Max(daTB5, daTB6) - .Min(daTB5, daTB6) + 1) / 30.4, 1)
SchnittTag = CDbl(TextBox10) / (daTB6 - daTB5)     ' Wert pro Tag
For ii = 0 To Year(daTB6) - intB5j
datVon = .Max(daTB5, DateSerial(intB5j + ii, 1, 1))
datBis = .Min(daTB6, DateSerial(intB5j + ii + 1, 1, 1))
Me("TextBox" & 13 + ii) = .Round(SchnittTag * (datBis - datVon), 2)
If ii < 3 Then Me("Label" & 24 + ii).Caption = "Jahr " & intB5j + ii
'           If ii > 2 Then Exit For ' warum das ?
Next ii
Else
TextBox25 = ""
End If
End With
End Sub

Wesentlich ist dabei auch, dass man bei Tages-Zeiträumen immer von Uhrzeit 00:00 ausgehen sollte,
für Start- und Endedatum. Deshalb habe ich aus dem 31.12. jeweils den 01.01. gemacht.
Da der User wohl das Endedatum mit 24:00 Uhr meint, steht bei daTB6 die 32 statt der 31.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

Hallo Erich,
recht herzlichen Dank für Deine erneute Rückmeldung und Deine Ausarbeitungen sowie Hinweise. Der Code läuft nun wunderbar und teilt die Summe entsprechend der Jahre auf und zeigt die jeweilige Summe nun auch i.d. entspr. Textboxes an. Ich habe dann Deine Hinweise bzgl. der Auskommentierung von Aufrufen aufgegriffen. Eine Fehlermeldung erscheint wohl offensichtlich nur dann, wenn OptionExplicit da steht (?); Die angesprochenen Codes dienen dazu den Eintrag bzw. die Änderung von Eintragungen i.d. entsprechenden Zelle über einen Kommentar zu dokumentieren. Wie müßte ich denn da evtl. die Spalte deklarieren? - Danke schon jetzt wieder für Deine Rückmeldung und nochmals herzlichen Dank für Deine bisherigen Mühen und Geduld mit mir.
Gruß - Wolfgang

Hi Wolfgang,
noch ein paar Hinweise/Tipps:
"Option Explicit" solltest du grundsätzlich verwenden - auch und besonders schon beim Schreiben des Codes.
Es hilft von vornherein, diverse Fehler zu vermeiden.
Wenn du in VBA im Menü Extras - Optionen, Karte "Editor"
die Option "Variablendeklaration erforderlich" aktivierst,
schreibt der VBA-Editor automatisch "Option Explicit" als erste Zeile in jedes neue Modul.
Zu Option Explicit schau auch mal z. B. hier: Hilfe zur Selbsthilfe
und hier:
http://www.online-excel.de/excel/singsel_vba.php?f=4
http://www.vbarchiv.net/faq/allg_optionexplicit.php
http://www.vb-seminar.de/vb_27.htm
Die Variable "spalte" (kommt in mind. 2 Prozeduren vor) solltest du jeweils als Long deklarieren
(grundsätzlich bei Variablen, die Nummern von Tabellenzeilen oder -spalten enthalten).
Noch eine Frage:
Wie gehst du mit Rundungsdifferenzen um, die bei solchen Verteilungen entstehen?
Die Summe der Jahresanteile muss ja jetzt nicht gleich dem Gesamtbetrag sein,
Cent-Differenzen sind wahrscheinlich.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

Hallo,
im gegenteil zu Erich habe ich Deine Tabelle nicht verstanden und
daher ein eigenes Beispiel aufgebaut so wie ich es verstanden habe.
https://www.herber.de/bbs/user/65916.xls
Gruß Tino

Hallo,
war von einem anderen Beitrag abgelenkt.
https://www.herber.de/bbs/user/65917.xls
Gruß Tino

Hallo Tino,
herzlichen Dank auch Dir für die erneute Rückmeldung; In Deinem Beispiel verändert sich die vorgegebene Summe nicht und wird für 2009 fest übernommen. Ich wollte aber erreichen, dass die Summe -Bsp. 50236, sofern z.B. Startdatum der 01.11.09 ist, die Zeit vom 01.11.09-30.11.09 noch für 2009 gerechnet wird und ab Dezember 2009 dann schon für 2010. Ändere ich Dein Beispiel vom Beginn 01.12.09 auf 01.11.09, dann bleibt die Summe für 2009 bei 50236 und fügt für die Zeit ab 01.12.09 noch 18838,5 für 2010 hinzu. Der Code sollte dabei aber die 50236 aufteilen. Der Code von Erich läuft auch so. Ich habe wohl "nur" noch irgendwelche "Böcke" an anderer Stelle. Herzlichen Dank nochmals und auch Danke für Deine Geduld mit mir. Mit meinen weiterhin sehr lückenhaften Kenntnissen in Excel und VBA erhalte ich hier immer sehr gute Hilfestellungen und Hinweise.
Gruß - Wolfgang

Hallo,
https://www.herber.de/bbs/user/65923.xls
Gruß Tino

Hallo,
nachdem ich meine Gäste verabschiedet hatte, habe ich mich sofort wieder ins Forum begeben. Erneut herzlichen Dank für Eure Rückmeldungen. Mit den Änderungen, Tino, läuft Dein Code auch so, wie ich es mir vorgestellt hatte. Nun kenne ich als Laie nicht die Unterschiede in den beiden Codes. Ich hatte daher schon den Code und die Änderungen von Erich eingebaut. Dir Erich auch recht herzlichen Dank für die weiteren Erläuterungen und Hinweise auf andere Verlinkungen zum Thema Excel/VBA. Die Frage zur Rundung stellt sich nicht in der Form, da sich relativ genaue Beträge ergeben und die minimalen Abweichungen kein Problem darstellen. Die Deklaration der Spalten habe ich auch nachgeholt. Danke Euch nochmals für Eure Geduld mit mir und für Eure Ausarbeitungen. Einen schönen (Rest)-Sonntag noch.
Herzliche Grüsse
Wolfgang