Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema Label
BildScreenshot zu Label Label-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema TextBox
BildScreenshot zu TextBox TextBox-Seite mit Beispielarbeitsmappe aufrufen

Summe auf Jahre aufteilen | Herbers Excel-Forum


Betrifft: Summe auf Jahre aufteilen von: Wolfgang
Geschrieben am: 11.11.2009 20:25:57

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

  

Betrifft: versuch mal und von: Tino
Geschrieben am: 14.11.2009 07:34:17

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


  

Betrifft: AW: Summe auf Jahre aufteilen von: BoskoBiati
Geschrieben am: 14.11.2009 09:07:35

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


  

Betrifft: AW: Summe auf Jahre aufteilen von: Wolfgang
Geschrieben am: 14.11.2009 10:32:53

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.


  

Betrifft: verstehe ich jetzt nicht... von: Tino
Geschrieben am: 14.11.2009 10:49:37

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


  

Betrifft: AW: verstehe ich jetzt nicht... von: Wolfgang
Geschrieben am: 14.11.2009 11:23:28

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


  

Betrifft: lade doch mal ein Beispiel hoch oT. von: Tino
Geschrieben am: 14.11.2009 11:34:53




  

Betrifft: AW: Summe auf Jahre aufteilen von: BoskoBiati
Geschrieben am: 14.11.2009 11:56:19

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


  

Betrifft: erwartet Listentrennzeichen von: Wolfgang
Geschrieben am: 14.11.2009 12:22:05

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


  

Betrifft: TextBox5)) + i - 1 von: Erich G.
Geschrieben am: 14.11.2009 13:15:36

(ohne weiteren Text)


  

Betrifft: AW: TextBox5)) + i - 1 von: Wolfgang
Geschrieben am: 14.11.2009 18:59:03

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



  

Betrifft: ich bleibe dabei bitte Beispiel hochladen... von: Tino
Geschrieben am: 14.11.2009 19:46:50

Hallo,
damit ich selbst spielen kann damit.

Gruß Tino


  

Betrifft: ohne Beispielmappe nur Trockenübung von: Erich G.
Geschrieben am: 14.11.2009 20:33:16

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


  

Betrifft: AW: ohne Beispielmappe nur Trockenübung von: Wolfgang
Geschrieben am: 15.11.2009 08:40:39

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


  

Betrifft: neuer Versuch von: Erich G.
Geschrieben am: 15.11.2009 10:57:03

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


  

Betrifft: AW: neuer Versuch von: Wolfgang
Geschrieben am: 15.11.2009 12:02:05

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


  

Betrifft: Option Explicit usw. von: Erich G.
Geschrieben am: 15.11.2009 12:53:39

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


  

Betrifft: AW: ohne Beispielmappe nur Trockenübung von: Tino
Geschrieben am: 15.11.2009 11:33:21

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


  

Betrifft: Korrektur hatte Datei nicht mehr gespeichert. von: Tino
Geschrieben am: 15.11.2009 11:42:59

Hallo,
war von einem anderen Beitrag abgelenkt.

https://www.herber.de/bbs/user/65917.xls


Gruß Tino


  

Betrifft: AW: Korrektur hatte Datei nicht mehr gespeichert. von: Wolfgang
Geschrieben am: 15.11.2009 12:20:25

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


  

Betrifft: hatte zweimal die gleiche TextBox in einer IF von: Tino
Geschrieben am: 15.11.2009 12:34:15

Hallo,

https://www.herber.de/bbs/user/65923.xls


Gruß Tino


  

Betrifft: Danke, Erich und Tino von: Wolfgang
Geschrieben am: 15.11.2009 17:13:46

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


Beiträge aus den Excel-Beispielen zum Thema "Summe auf Jahre aufteilen"