Automatic Goal Seek That References Cell For Target Value

I am trying to create a VBA code that does two things: automatically runs goal seek to update as inputs change, and references a cell containing a formula for the target value.

I want to:

Set cell I24
To the value that is shown in cell G9 (which contains a formula, and the value in the cell will change as other inputs change)
By changing cell F24

Any help on a VBA code that would accomplish this?

Thank you!

Welcome to the forum!

If you can attach a file and show what your manual run's results were, we can then work on the automation. To attach a file, click the Go Advanced button in lower right of a reply. Click the paperclip icon in the toolbar and Browse and Upload the file.

Goalseek and solver can be similar. See these examples and links to get you going. Once the file is added, code can be added to run a routine if the cell I24 is changed.

Please Login or Register  to view this content.

To run GoalSeek automatically you could probably use the Private sub Worksheet_Change event. But as you seem to wish that GoalSeek should run when the value in G9 changes and as G9 contains a formula this requires a somewhat different approach. I think doing it this way should work.

Start by right clicking the tab on sheet1 and select “View code”

To the left you see under “Microsoft Excel Objects” an number of sheets and “ThisWorkbook”

Right click on “ThisWorkbook” and click “View Code”. In the windows that opens paste this code

Please Login or Register  to view this content.

Then right click on Sheet1 and click “View Code”. In this window you paste this code

Please Login or Register  to view this content.

Save the workbook, close it. Open it again an make a change that should give you a changed value in G9 and see if GoalSeek fires.

Alf

Please Login or Register  to view this content.

I have a similar problem to what has been discussed here. I am trying to automate one way or another the Goal Seek function in a spreadsheet such that it can either bur run by a macro or when an entry has been changed. I tried to run as a macro but the goal seek value seems like it needs to be hard coded and cannot reference a cell or be copied into the dialogue box. Any help would be greatly appreciated. TIA

Hi

Welcome to the forum but ........

I have a similar problem to what has been discussed here.

as forum rule #2 says that

Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread. You may include up to 3 links to other URLs in a single post, no more, so only link to the relevant pages.

So start a thread on your own and most likely a member will help you. And by the way a "new" thread attracts more attention then an "old" with several postings.

Alf