Macro to calculate billing schedule?

jmthompson

Well-known Member
Joined
Mar 31, 2008
Messages
966
Good afternoon,
I have just been passed a new VBA project and there is one component I would like help with.

Column J lists a start date
Column K lists an end date
Column N lists a dollar amount.
Columns P through the last column has headers of month-year in text form(for example May-08)

The user enters contract information in the spreadsheet including the above cells. What they want to automate is once the user enters the contract information on a given row, she runs a macro to determine the billing schedule by calculating how many months there are between the start and end dates, divide the dollar amount in column N by that number and then prompt the user to enter what month the first payment is due, once that is entered, the macro should place the payment amount in that column and all applicable subsequent columns.

For example,
Column J = 6/1/2008
Column K = 9/1/2008
Column N = 3,000
The macro should calculate 3 months, the monthly payment amount is $1000.
A starting month prompt appears and the user enters July-08. The macro then places 1,000 in the columns for July-08, August-08 and September-08.

Is such a thing possible?
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Okay, I was processing this in my sleep last night.

I think I will build the macro to add 2 columns, one (p)to calculate the number of months in the given range, one (q)to calculate the monthly billing amount.

What I still need to figure out-

1. How to create a popup that asks the user what month to begin the billing.

2. How to write the code to translate the month entered as a specific cell

3. How to write the macro to fill in the billing amount (from column q) in that cell and the number of cells to the right of that cell indicated by column p.

4. I have never written a macro to perform on the active row, so Icould use some help there, as well.

Any ideas would be appreciated.
 
Upvote 0
The active cell/row thing is interesting. Are you stipulating that the user must ensure that the active cell is in the same row as the data they have just entered for the macro to run correctly? I just worry that your users will sometimes enter their data, hit enter after the last bit of data entry, call the macro and it will try to run on the row below the one it should be.

IF (could be a big if) the user is always appending data to the bottom of the file (i.e. first unused row), it might be more appropriate for the macro to assume that it should act on the last row in the file.

Or, you could have the macro first ask the user to confirm the row they want it to act upon.

It's a call for you.

More practically, re: 1, you can get user input via InputBox, e.g.:
lngMonth = Application.InputBox("Enter the 1st billing month", Type:=1)
type = 1 forces the input to be a number. See VBA help for more.
 
Upvote 0
(When I say 'see VBA help for more', I mean for more on the type parameter for the InputBox method...not for the whole rest of your query!)
 
Upvote 0
slomobonobo-
Thank you for your reply.

Unfortunately, the user will not always be working on the last row, they will have to ensure the cell they are in is in the appropriate row. This user is well-versed in Excel, so I do not forsee a problem there.

Re: Input Box
I am now thinking of using two input boxes, one asking how many monthly payments will be due (replaces the date range calculation previously mentioned) and one asking how many months from NOW those payments begin.

Could I write code to recognize what month we are in and then add the value previously calculated in the cells offset by the number entered in the second by from this month's cell?

I guess another question would be how to assign months to each column...
 
Upvote 0
HI,

Just a suggestion.
Try a worksheet change event.
If the last entry will be the dollar amount set the target to that column and when the user tabs off of that cell the macro is activated. The code will know the active cells row and therefore with coding finish populating the rest of the desired info in the correct row and columns.
 
Upvote 0
Charles- a worksheet change event is a great idea! What will the activecell be once the macro is activated, the cell to the right of the targeted column or the altered cell in the targeted column?
 
Upvote 0
Hi,

On my way out, but the active cell will be the cell that was in the target column, such as column "C" was the target and the cell that the info was entered will be the active cell.
 
Upvote 0
I saw this post yesterday and ignored it because I am pretty sure that this could be done easily enough without VBA. I figured one of the function people would jump on this? Anyway, if you are settled on using code, you might consider using a simple userform to gain more control. It would be less complicated then what you guys are throwing around now... If your interested, I'll post more. The code to figure your dates would be simple enough...
 
Upvote 0
I am open to anything...Okay, I just did some reading on userforms (have never used them before).

I would want to set it up that the user selects the row to modify and then clicks a macro button.

I would want the macro to begin by removing any data in select columns.

For my needs, I think I would include fields to capture:
RTBB date
Number of installments
Begin monthly billing date

Once they click on my enter button, I would want the RTBB date entered into its appropriate cell, I think I understand how to do this.

I have no idea where to begin with the following:

The number of installments and begin monthly billing date is trickier.
I would want to use the number of installments data in 2 ways:
First to determine the billing amount (divide the net amount by the number of installments)
Second to determine how many cells to enter the billing amount into beginning with the Begin monthly billing date.

For the Begin monthly billing date, I would like this information to tell the macro where to begin entering the billing amount. The data entered would match the appropriate column header.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

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