Excel macro help urgently required!!!

[mattyhigginsuk]

Hi,

I have detailed my simple calculation below. A balance is derived from Cost, Deposit and Part Ex and then the repayment is calculated based on a balance, interest rate and loan term. I have no experience whatsoever of macros but believe that it is possible to insert command buttons next to any of the variables (where I've put SOLVE) that when clicked will actually recalculate that value in line with any changes you've made to the others??? Question is, how on earth do you do it?????? HELP!!!!!!

Cost 20000 SOLVE
Deposit 2000 SOLVE
Part Ex 3500

Balance 14500
Flat Rate 6.50% SOLVE
Term 84 SOLVE

Repayment £251.16

 

[crook_101]

Hi,

No macro necessary - the following will do what you want

Excel Workbook
	A	B
1	Cost	20000
2	Deposit	2000
3	Part Ex	3501
4	Balance	14499
5	Flat Rate %	6.5
6	Term (months)	84
7
8	Repayment	215.30
Sheet4

Cheers,
Ian

 

[mattyhigginsuk]

Thanks for your reply Ian. I don't think I explained my problem particularly well, as I'd got the formula sorted for the calculations. What I was after was how to make a click button that recalculated one of the variables if any of the others was changed. For example, if the payment was £278 and I wanted to find out what the new deposit needed would be if I only wanted to pay £260 a month? I was after buttons next to the cost, deposit, period and rate. Any ideas how this can be done??

 

[Ruddles]

You need four macros: one that will calculate Cost given all the other values, one that will calculate Deposit given all the other values, one that will calculate Flat Rat given all the other values and one that will calculate Term given all the other values.

Each macro will need to check that all its required values are present, valid, compatible and sensible, then perform the appropriate type of calculation and place the result in the correct worksheet cell.

Then create four buttons and place them next to the Cost, Deposit, Flat Rate and Term boxes, and point each one at the relevant macro.

How good is your VBA?

 

[mattyhigginsuk]

I'm afraid my VBA is absolutely non existent!! I don't think I appreciated the complexity of it all when I asked the question. Is there any guidance you can give or is it quite time consuming? I don't want to put you out on account of my total lack of knowledge!

 

[Ruddles]

I'm about to leave the office but to be honest this is an ideal simple project to cut your teeth on.