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

#### kneb7900

##### New Member
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:

#### rosenbe

##### Board Regular
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.

#### kneb7900

##### New Member
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

##### Board Regular
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``````

#### kneb7900

##### New Member
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

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

#### kneb7900

##### New Member
Awesome! Thank you so much for your help.

1,082,259
Messages
5,364,103
Members
400,779
Latest member
lumers

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...