# Automatic goal seek program

1. ## Automatic goal seek program

Dear Experts
I am new to this forum and this is my first view on word macros and VBA.I am also Excel beginner.

How to write automatic goal seek program in excel 2013.

I have an input value from 'Input value' sheet that goes to a goal seek tab.

If i change target goal seek value in Cell F16(goal page sheet) it automatically should change the cell value(Cell E7) in next sheet(input value sheet E7) to match the required answer in H7(goal page sheet)

Please let me know the step by step procedure to create this. I don't know if i need to create a button.

Regards
Bala

2. ## Re: Automatic goal seek program

Does it have to be done by goal seek? It seems to me that the algebra involved is very simple, in this case, so that it will be much easier to solve the problem algebraically rather than try to automate goal seek. Correct me if I'm wrong but it looks like the problem is:

F(O2)=F(total)*y(O2)
Given F(O2) and y(O2), solve for F(total).

Then program that formula into your total flow cell. Is there more to the problem than that?

3. ## Re: Automatic goal seek program

Actually i have presented the simple way to explain the problem.
In reality for my use. There are 10 different inputs added to front page and only 1 of the input is a deciding factor . Hence I have requested automatic goal seek.

I actually needed this for chemical balance sheet . I hope you can help me to write a program in VBA or macros for automatic goal seek.
Thanks

4. ## Re: Automatic goal seek program

1) I am not real good with chemical balance problems, but it seems that they are mostly "solve a system of equations" type of problem. Even though the math is now more complicated than the original question, I would still tend to work through some of it. Many of these type of problems that I have done end up being "linear" equations, which means they can be solved using "Gaussian elimination" and/or "matrix inversion" which is easily automated in Excel using the built in MINVERSE() (and maybe other matrix functions) function.

2) If it really turns out that it must be solved using numerical methods, then Goal Seek can be automated in VBA using the Goal Seek method: https://msdn.microsoft.com/en-us/lib...ce.11%29.aspx# At this point I am assuming you are familiar with VBA. If not, then I would recommend that you review Excel's object model and other introduction to VBA type resources. It is often easiest to start with the macro recorder, record yourself running the Goal Seek manually, then use that as the "core" of your VBA procedure. Depending on the level of automation required, you may want to associate the procedure with the calculate event, if you are familiar with event procedures.

3) I tend to prefer Solver over Goal Seek, just because it is a more robust utility. Solver uses the same Newton-Raphson type algorithm that Goal Seek does, but has other options as well. Solver can be programmed into VBA as well in a similar fashion.

4) If you are familiar with the NR and other numerical algorithms, it is possible to program those into a spreadsheet and bypass the use of Goal Seek. Many times, if I want to automate a calculation like this, I will prefer to program my own NR algorithm rather than code goal seek into VBA.

As you can see, I tend to use "automate Goal Seek using VBA" as my last resort. It can be done, and the help file contains examples. We also have many examples around the forum. Since I try to avoid it, I am not very good at coding Goal Seek in VBA (though it doesn't seem that difficult). If this is the direction you would like to go, then let us know where we need to start.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1