Goal seek VBA with IF statements

[guyza]

I am trying to run an automatic goal seek via a button and am not sure what the Macro <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> code should be. The forumla should ideally auto calculate (without having to press the button if possible).

The calculation is to determine an initial loan repayment amount (cell I10).
Depending on the term of the loan (cell d7 [measured in months) the cell to be modified will change (column M).

Essentially for every year the cell reference in column M increases by 12.
i.e. for 12 months = M32, 24 month = M44.....for 84 months=M104

All information is based in worksheet 'Rental Schedule (2)'

if d7 = 36 then goal seek cell M56 to equal zero by changing cell i10
if d7 = 48 then goal seek cell M68 to equal zero by changing cell i10
if d7 = 60 then goal seek cell M80 to equal zero by changing cell i10
if d7 = 72 then goal seek cell M92 to equal zero by changing cell i10
if d7 = 84 then goal seek cell M104 to equal zero by changing cell i10

Please help.

 

[Jeffrey Lopez]

Not 100% sure exactly what you are trying to do, but I hope this points you in the right direction.

You can use an INDIRECT/ADDRESS to change where you are looking for values on the fly. For example:

=INDIRECT(ADDRESS(D7+20,13))

This is just saying pull the values that is in Column 13 Row (D7+20) so 12 = 32, 24 = 44, 36 = 56 and so on... So basically if D7 is 36, then it will return the values in M56...

Hope that at least points you in right direction!

 

[guyza]

Thanks Jeffrey

So what would the VBA code be?
This is what I currently have?

Sub Goalseekstepup()


' Goalseekstepup Macro
' Goal seek step up repayments
'    Sheets("Rental Schedule  (2)").Range("M80").Goalseek Goal:=0, ChangingCell _
        :=Sheets("Rental Schedule  (2)").Range("I10")
End Sub