Live-Forum - Die aktuellen Beiträge
Datum
Titel
19.04.2024 12:58:59
Daten einfügen aus anderem Sheet
19.04.2024 12:23:24
Wenn-Formel anpassen
19.04.2024 12:09:19
Unique (Application.WorksheetFunction.Unique)
19.04.2024 11:51:37
Nächsten identischen Treffer suchen
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1116to1120
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1116to1120
1116to1120
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Summe auf Jahre aufteilen

Summe auf Jahre aufteilen
Wolfgang
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  2 Then Exit For
Next i
Else
TextBox25 = ""
End If
End With
End Sub

versuch mal und
14.11.2009 07:34:17
Tino
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
AW: Summe auf Jahre aufteilen
14.11.2009 09:07:35
BoskoBiati
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
Anzeige
AW: Summe auf Jahre aufteilen
14.11.2009 10:32:53
Wolfgang
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.
Anzeige
verstehe ich jetzt nicht...
14.11.2009 10:49:37
Tino
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
AW: verstehe ich jetzt nicht...
14.11.2009 11:23:28
Wolfgang
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
Anzeige
lade doch mal ein Beispiel hoch oT.
14.11.2009 11:34:53
Tino
AW: Summe auf Jahre aufteilen
14.11.2009 11:56:19
BoskoBiati
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
erwartet Listentrennzeichen
14.11.2009 12:22:05
Wolfgang
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
Anzeige
TextBox5)) + i - 1
14.11.2009 13:15:36
Erich
(ohne weiteren Text)
AW: TextBox5)) + i - 1
14.11.2009 18:59:03
Wolfgang
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  2 Then Exit For
Next i
Else
TextBox25 = ""
End If
End With
End Sub

Anzeige
ich bleibe dabei bitte Beispiel hochladen...
14.11.2009 19:46:50
Tino
Hallo,
damit ich selbst spielen kann damit.
Gruß Tino
ohne Beispielmappe nur Trockenübung
14.11.2009 20:33:16
Erich
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  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
Anzeige
AW: ohne Beispielmappe nur Trockenübung
15.11.2009 08:40:39
Wolfgang
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
Anzeige
neuer Versuch
15.11.2009 10:57:03
Erich
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  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
Anzeige
AW: neuer Versuch
15.11.2009 12:02:05
Wolfgang
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
Anzeige
Option Explicit usw.
15.11.2009 12:53:39
Erich
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
AW: ohne Beispielmappe nur Trockenübung
15.11.2009 11:33:21
Tino
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
AW: Korrektur hatte Datei nicht mehr gespeichert.
15.11.2009 12:20:25
Wolfgang
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
Danke, Erich und Tino
15.11.2009 17:13:46
Wolfgang
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

Links zu Excel-Dialogen

Label TextBox

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige