VBA Code to run a goal seek

Hi There,

I am in the first tab called "Settings".
In my 5th tab (called 'Model'), there is a model I want to calculate. But the 'Calculate' button is on the first tab 'Settings'.

The question is, how to I get my macro to run a goal seek on information from the 5th tab. I tried this. It has not worked.

Worksheets("Model").Range("B19").GoalSeek Goal:=0.15, ChangingCell:=Worksheets("Model").Range("B16")

Ther eis a hardcode there for the 15% (0.15) I want to set as a target. Ideally I would like to link this to a cell in the first sheet 'Settings'.

Help would be much appreciated.

Thanks

Thanks for the introduction, much appreciated.

Please see my test sheet attached. I have given more detail on the first tab as to what I am trying to achieve. Help would be greatly appreciated.

Thanks

Any help people?

Originally Posted by MrShorty

I am not real skilled at calling Goal Seek from VBA. My thought on approaching this problem.

Rather than requiring VBA/Excel/myself to look back at "settings" to get the target return rate, I would tend to link this up on "model", then add another helper cell that will calculate the calculated IRR (G12 or G13)-target. Then my goal seek will always be "seeking" a target value of 0.

See attachment. I have added cells to model to 1) choose equity or capital, and 2) calculate the difference between target IRR and calculated IRR. 3) Goal seek code is unchanged,except for references to target cell and target value. I have also added a link on "result" to the value determined from the Goal seek. I went for a "minimal use of VBA" approach, which means the only VBA statement used is the goalseek statement.

Thank you so much, really appreciate it.

Nice to know my goalseek part of my code was correct. Might become half decent at this some day