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:

rosenbe

Board Regular
Joined
Nov 24, 2011
Messages
67
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
Joined
Nov 29, 2011
Messages
12
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
Joined
Nov 24, 2011
Messages
67
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
Joined
Nov 29, 2011
Messages
12
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
Joined
Nov 24, 2011
Messages
67
Sorry, wrong function, it should be DateSerial, you are getting that error because Date does not have any parameters.
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top