jay20aiii
04-05-2012, 07:47 AM
Hi, I'm having a go at making a spreadsheet in excel to automatically work out how much has been paid in bills each month based on the current date.
Firstly I run this VBA Macro on a bunch of cells to calculate what date should be displayed for each of my payments:
Function NextPayment(day As Integer) As Date
If day > DatePart("d", Now) Then
NextPayment = DateValue(day & "/" & Month(Now) & "/" & Year(Now))
Else
If DatePart("m", Now) = 12 Then
NextPayment = DateValue(day & "/01/" & Year(Now) + 1)
Else
NextPayment = DateValue(day & "/" & Month(Now) + 1 & "/" & Year(Now))
End If
End If
End Function
I just want it to either put the payment date into the relevant cell, or if the payment would have been paid (that day of the month has been exceeded) then add a month to the date to prepare it for Next months calculation.
Then I use those dates in this VBA Macro to try and calculate how much has yet to be paid based on the current date:
Function SumBills(Dates As Range, Values As Range) As Double
For x = 1 To Dates.Rows.Count
If DatePart("d", Now) > 17 Then
SumBills = SumBills + Values(x, Values.Columns.Count).Value
Else
If DatePart("m", Dates(x, Dates.Columns.Count).Value) = DatePart("m", Now) Then
SumBills = SumBills + Values(x, Values.Columns.Count).Value
End If
End If
Next
End Function
The Dates argument is a range of cells that contain the dates that the payments will be made.
The Values argument is a range of adjacent cells that contain the value of that particular bill.
If the current date is beyond the 17th of the Month then it just adds up all the values because they will all be due at the start of the next month.
Otherwise I am just checking the Month value (since I already formatted that in the previous function).
If the Month Value matches the Month value of todays date then I add up the values of those cells to get the total amount due to be paid.
Trouble is, it worked when I made the spreadsheet. But if I open the spreadsheet from day to day, it never recalulates the SumBills function. Or maybe it does, but there is something wrong with the logic? "Caluclate Now" and "Calculate Sheet" makes no difference.
How do you get a spreadsheet to automatically calculate itself when you open it, and factor in the current date? Have I missed something? Or can you just not do it? Or maybe theres another way that works better?
Any help appreciated
Firstly I run this VBA Macro on a bunch of cells to calculate what date should be displayed for each of my payments:
Function NextPayment(day As Integer) As Date
If day > DatePart("d", Now) Then
NextPayment = DateValue(day & "/" & Month(Now) & "/" & Year(Now))
Else
If DatePart("m", Now) = 12 Then
NextPayment = DateValue(day & "/01/" & Year(Now) + 1)
Else
NextPayment = DateValue(day & "/" & Month(Now) + 1 & "/" & Year(Now))
End If
End If
End Function
I just want it to either put the payment date into the relevant cell, or if the payment would have been paid (that day of the month has been exceeded) then add a month to the date to prepare it for Next months calculation.
Then I use those dates in this VBA Macro to try and calculate how much has yet to be paid based on the current date:
Function SumBills(Dates As Range, Values As Range) As Double
For x = 1 To Dates.Rows.Count
If DatePart("d", Now) > 17 Then
SumBills = SumBills + Values(x, Values.Columns.Count).Value
Else
If DatePart("m", Dates(x, Dates.Columns.Count).Value) = DatePart("m", Now) Then
SumBills = SumBills + Values(x, Values.Columns.Count).Value
End If
End If
Next
End Function
The Dates argument is a range of cells that contain the dates that the payments will be made.
The Values argument is a range of adjacent cells that contain the value of that particular bill.
If the current date is beyond the 17th of the Month then it just adds up all the values because they will all be due at the start of the next month.
Otherwise I am just checking the Month value (since I already formatted that in the previous function).
If the Month Value matches the Month value of todays date then I add up the values of those cells to get the total amount due to be paid.
Trouble is, it worked when I made the spreadsheet. But if I open the spreadsheet from day to day, it never recalulates the SumBills function. Or maybe it does, but there is something wrong with the logic? "Caluclate Now" and "Calculate Sheet" makes no difference.
How do you get a spreadsheet to automatically calculate itself when you open it, and factor in the current date? Have I missed something? Or can you just not do it? Or maybe theres another way that works better?
Any help appreciated