Macro to perform goalseek - help!!

[mattyhigginsuk]

Hi,

I have a calculator that derives a monthly payment by accounting for net capital amount (combination of total capital amount, deposit and part exchange), flat interest rate and loan period. I need to make it all singing and dancing, in particular by inserting buttons next to price, deposit,flat rate and term which will invoke a recalculation of that variable based on the changes made to any or all of the others.
I have no idea about macros and would be really grateful for any assistance offered as to how I can do this. I have used the goal seek function before but need something to automate it and that can use the contents of a cell as the goal.
Is this possible???

Thanks

 

[VoG]

GoalSeek is available in VBA - example from the Help

<code>Worksheets("Sheet1").Range("Polynomial").GoalSeek _
    Goal:=15, _
    ChangingCell:=Worksheets("Sheet1").Range("X")

</code></pre>

 

[mattyhigginsuk]

Thanks for your reply. Only issue I have is that the goal seek figure is a variable (the contents of one of the cells) rather than a specific number and goal seek doesn't seem to like this. Any ideas at all anyone????

 

[Sean 35813]

This might work.

Enter the initial value of the debt in E3, the number of periods to repay the debt in E7, and the monthly repayment value in E9.
<o> </o>
Then create the following Macro (and assign it to a button). This gives you the interest rate needed to complete the maths in E7.

Sub Interest()
  Range("A1").Formula = "=PMT(E5,E7,E3)"
  Range("A1").GoalSeek Goal:=Range("E9").Value, _
  ChangingCell:=Range("E5")
  End Sub