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

[kneb7900]

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.

 

[rosenbe]

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:

ws.Cells(iRow, 1).Value = ws.Cells(iRow, 1).Value + _
                          (7 * (Me.textNumberRecurrences.Value - 1) + 1)

Hope that helps.

 

[kneb7900]

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.

 

[rosenbe]

You can try something like this.

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

 

[kneb7900]

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.

 

[rosenbe]

Sorry, wrong function, it should be DateSerial, you are getting that error because Date does not have any parameters.

 

[kneb7900]

Awesome! Thank you so much for your help.