Distribution based on dates

[Stevenn]

I am trying to distribute an amount in some months based on a StartDate and EndDate. I guess I've found a method to do it manually, but I want to do it all in automatically in VBA only based on the three 'variables'.

I've tried to parse what I've done down below. I hope it's understandable. I will appreaciate any suggestions. I've tried almost everything

<table border="0" cellpadding="0" cellspacing="0" width="1561"><colgroup><col style="mso-width-source:userset;mso-width-alt:3035; width:62pt" span="2" width="83"> <col style="mso-width-source:userset;mso-width-alt:3766;width:77pt" width="103"> <col style="mso-width-source:userset;mso-width-alt:3035;width:62pt" width="83"> <col style="mso-width-source:userset;mso-width-alt:3986;width:82pt" width="109"> <col style="mso-width-source:userset;mso-width-alt:3035; width:62pt" span="12" width="83"> <col style="mso-width-source:userset;mso-width-alt:3803;width:78pt" width="104"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:62pt" height="20" width="83">
</td> <td style="width:62pt" width="83">
</td> <td style="width:77pt" width="103">
</td> <td style="width:62pt" width="83">
</td> <td style="width:82pt" width="109">
</td> <td class="xl68" style="width:62pt" width="83">31</td> <td class="xl68" style="width:62pt" width="83">28</td> <td class="xl68" style="width:62pt" width="83">31</td> <td class="xl68" style="width:62pt" width="83">30</td> <td class="xl68" style="width:62pt" width="83">31</td> <td class="xl68" style="width:62pt" width="83">30</td> <td class="xl68" style="width:62pt" width="83">31</td> <td class="xl68" style="width:62pt" width="83">31</td> <td class="xl68" style="width:62pt" width="83">30</td> <td class="xl68" style="width:62pt" width="83">31</td> <td class="xl68" style="width:62pt" width="83">30</td> <td class="xl68" style="width:62pt" width="83">31</td> <td style="width:78pt" width="104">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">StartDate</td> <td class="xl63" style="border-left:none">EndDate</td> <td class="xl63" style="border-left:none">NumberOfDays</td> <td class="xl63" style="border-left:none">Amount</td> <td class="xl63" style="border-left:none">AmountEachDay</td> <td class="xl63" style="border-left:none">January</td> <td class="xl63" style="border-left:none">February</td> <td class="xl63" style="border-left:none">March</td> <td class="xl63" style="border-left:none">April</td> <td class="xl63" style="border-left:none">May</td> <td class="xl63" style="border-left:none">June</td> <td class="xl63" style="border-left:none">July</td> <td class="xl63" style="border-left:none">August</td> <td class="xl63" style="border-left:none">September</td> <td class="xl63" style="border-left:none">October</td> <td class="xl63" style="border-left:none">November</td> <td class="xl63" style="border-left:none">December</td> <td class="xl64" style="border-left:none">TOTAL=Amount</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20" align="right">15-03-2012</td> <td class="xl65" style="border-top:none;border-left:none" align="right">20-04-2012</td> <td class="xl66" style="border-top:none;border-left:none" align="right">36,00</td> <td class="xl67" style="border-top:none;border-left:none" align="right">200</td> <td class="xl67" style="border-top:none;border-left:none" align="right">5,555555556</td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none" align="right">88,88888889</td> <td class="xl66" style="border-top:none;border-left:none" align="right">111,11</td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none" align="right">200</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20" align="right">12-03-2012</td> <td class="xl65" style="border-top:none;border-left:none" align="right">29-05-2012</td> <td class="xl66" style="border-top:none;border-left:none" align="right">78,00</td> <td class="xl67" style="border-top:none;border-left:none" align="right">200</td> <td class="xl67" style="border-top:none;border-left:none" align="right">2,564102564</td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none" align="right">48,71794872</td> <td class="xl66" style="border-top:none;border-left:none" align="right">76,92</td> <td class="xl66" style="border-top:none;border-left:none" align="right">74,36</td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none" align="right">200</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20" align="right">07-03-2012</td> <td class="xl65" style="border-top:none;border-left:none" align="right">25-06-2012</td> <td class="xl66" style="border-top:none;border-left:none" align="right">110,00</td> <td class="xl67" style="border-top:none;border-left:none" align="right">200</td> <td class="xl67" style="border-top:none;border-left:none" align="right">1,818181818</td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none" align="right">43,63636364</td> <td class="xl66" style="border-top:none;border-left:none" align="right">54,55</td> <td class="xl66" style="border-top:none;border-left:none" align="right">56,36</td> <td class="xl66" style="border-top:none;border-left:none" align="right">45,45</td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none" align="right">200</td> </tr> </tbody></table>

 

[Stevenn]

I've tried with following code and it works if the two dates are in the same year. In the example below it will only make msgboxes for months between month number 04 and month number 07 for both 2012 and 2013.

Option Explicit

Public Sub Distribution()

    Dim Amount          As Integer
    
    Dim StartDate       As Date
    Dim EndDate         As Date
    
    Dim AmountPerDay    As Double
    
    Dim lngYearNumber   As Long
    Dim lngMonthNumber  As Long
    
    Let Amount = 200
    Let StartDate = "03-04-2012"
    Let EndDate = "08-07-2013"
    
    Let AmountPerDay = Amount / (EndDate - StartDate)
    
    If VBA.Month(StartDate) = VBA.Month(EndDate) Then
        MsgBox Amount
    Else
        For lngYearNumber = VBA.Year(StartDate) To VBA.Year(EndDate)
            For lngMonthNumber = VBA.Month(StartDate) To VBA.Month(EndDate)
                If lngMonthNumber = VBA.Month(StartDate) Then
                    MsgBox (VBA.Day(VBA.DateSerial(2012, lngMonthNumber + 1, 0)) - VBA.Day(StartDate)) * AmountPerDay
                ElseIf lngMonthNumber = VBA.Month(EndDate) Then
                    MsgBox VBA.Day(EndDate) * AmountPerDay
                Else
                    MsgBox VBA.Day(VBA.DateSerial(2012, lngMonthNumber + 1, 0)) * AmountPerDay
                End If
            Next lngMonthNumber
        Next lngYearNumber
    End If

End Sub