Creating a recurring financial transaction (e.g. monthly rent)

kneb7900

New Member
Joined
Nov 29, 2011
Messages
12
SET UP:
I've created a user form where a person can enter information such as the date, explanation of transaction, amount, recurrence pattern (weekly, bi-weekly, or monthly), and number of recurrences. That information is then transferred to the worksheet, changing the date by the corresponding interval for the given number of recurrences.

This is meant to be used in a budgeting sheet where the user expects a recurring income (e.g. bi-weekly work cheques) or expense (e.g. monthly rent).

PROBLEM:
For each transaction, the date ends up being thrown off from the original if the number of recurrences is high.
For example:
Entering:
2011-11-29, Test (as explanation), 85 (as amount), weekly, 9 recurrences
Gives: Proper information except the dates. The dates I end up with are:
2011-11-29, 2011-12-03, 2011-12-06, 2011-12-10, 2011-12-13, 2011-12-17, 2011-12-20, 2011-12-24, 2011-12-27
However, when I put in a lower number of recurrences (say 5), I get the right dates.

CODE:
Here is a brief example of the code for a weekly recurrence transaction:

Do

iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1,0).Row

If optionWeek = True Then
If Me.textNumberRecurrences.Value - 1 > 0 Then
ws.Cells(iRow, 1).Value = ws.Cells(iRow, 1).Value + _
Day(7 * (Me.textNumberRecurrences.Value - 1) + 1)
End If
End If

[code for other possible recurrence patterns (bi-weekly, monthly)]
[code for entering other user input (explanation, amount)]

Me.textNumberRecurrences.Value - 1

Loop Until Me.textNumberRecurrences = 0

[code to re-order transactions from earliest to latest]


Feel free to ask me questions for more clarification of the problem if needed.
 
Last edited:

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
At least one of your problems (and hopefully the only) is the Day function, Day(date) returns the day of the month from date, that is, if you use 7*9 (63) as your date then it will return 3 as 63 is March 3, 1900. (A date is a positive integer that is the number of days since January 0, 1900)

This should work better:
Code:
ws.Cells(iRow, 1).Value = ws.Cells(iRow, 1).Value + _
                          (7 * (Me.textNumberRecurrences.Value - 1) + 1)

Hope that helps.
 
Upvote 0
Great, thanks! It works once you take out the +1 at the end (which seemed to be needed in my earlier coding).

Now, I'm just not sure how to go about creating a monthly recurrence. I'm sure I could write a long list of conditional statements depending on the year and month, but I'm wondering if there might be an easier way.
 
Upvote 0
You can try something like this.
Rich (BB code):
If Me.textNumberRecurrences.Value - 1 > 0 Then
Dim startDate As Long
startDate = ws.Cells(iRow, 1).Value
ws.Cells(iRow, 1).Value = Date(Year(startDate), Month(startDate) + Me.textNumberRecurrences.Value - 1, Day(startDate))
End If
 
Upvote 0
Alright. That's similar to the code I have, but without defining startDate. I added that part as well.

The error I get in both situations is that I am missing a ')' but I've looked over the code dozens of times and even tried copying yours directly and I don't see where it has gone wrong.
 
Upvote 0
Sorry, wrong function, it should be DateSerial, you are getting that error because Date does not have any parameters.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top