Macro to run multiple goal seeks on different rows

[goldfishhub]

Hi,

I'm need of help for a macro to run multiple goal seeks on the same column but on different rows for a standard rental calculation. I've attached the excel template i'm working on.

Column A is just the row numbers, the macro needs to run over a different number of rows as each sheet I have in my file has different row numbers. It could be 10 rows it could be 5,000 rows.

Column B : this is a fixed rent amount (goal seek has to equal this rent amount), cannot change

Column C : this is a fixed interest amount, cannot change

Column D : fixed amount, cannot change

Column E : this is a variable and CAN change

Column F : this is a calculation

Column G : calculation showing the difference. once the goal seek has finished I hope this cell to be 0


the goal seek needs to set the cell in column F to equal the cell in column B by changing the cell in column E

I hope this makes sense

in the example I've shown 2 rows however the macro needs to continue on and run as many rows as I have



very much appreciated your help!

thanks

[MrShorty]

I'm sure a macro could do this. Before exploring the macro programming, I would ask what the difference is between what you want the macro to do and the built in NPER() function? Each of the quantities in the PV formula has an Excel function. It looks like you are trying to find the number of periods [NPER()] that gives the desired payment at a given interest rate and present value. While it is certainly possible to program the spreadsheet to find the number of periods that makes PMT() equal a certain value, it seems easier to just use the NPER() function. Is there a reason you are avoiding the NPER() function?

If it helps, as a test, I put =NPER(C5/12,B5,-D5,0,0) into E5 and G5 immediately returned 0. Copied down into row 6 and G6 also became 0, so it seems to work.

Help file for the NPER() function: https://support.office.com/en-us/art...f-b6a38151d815