My spreadsheet allows the user to enter various assumptions and auto
calculates output in the form of a 20 year rent schedule. One option is for
rent to rise at annual amounts chosen by the user so the starting rent is
calculated via goalseek to match the NPV of the rising rent to a calculated
result.

I want to create a table that displays the year 1 starting rent for
different interest rates but I can't see how to get the goalseek macro to
run when calculating the table so I just get one result repeated.

Am I trying to do the impossible or can somebody show me how to do it?
(ps if the solution is to write a macro to calculate for each interest rate
and copy the result to an output cell I can do that but I was just hoping I
could do it automatically with a table).


If a user changes an assumption the goalseek macro runs automatically
through the code below:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Application.Intersect(Target, Range("C3:H52")) Is Nothing Then Exit Sub
Application.EnableEvents = False
SetRisingRent
Application.EnableEvents = True
End Sub