+ Reply to Thread
Results 1 to 4 of 4

Automatic goal seek program

  1. #1
    Registered User
    Join Date
    01-20-2015
    Location
    UK
    MS-Off Ver
    2013
    Posts
    2

    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.

    I attached this spreadsheet now

    Please guide me.

    Regards
    Bala
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,467

    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?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    01-20-2015
    Location
    UK
    MS-Off Ver
    2013
    Posts
    2

    Re: Automatic goal seek program

    Thanks for the reply.

    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. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,467

    Re: Automatic goal seek program

    A few additional thoughts:

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Excel VBA Multiple Automatic Goal Seek Required Please Assist us
    By champaben in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-20-2014, 04:22 PM
  2. [SOLVED] Automatic Goal Seek
    By Talazem in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-04-2013, 11:28 AM
  3. Faster calculation for multiple automatic goal seek
    By lockd in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-26-2013, 09:30 AM
  4. Automatic Goal Seek VBA won't work now
    By Brodie05 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-25-2013, 08:43 PM
  5. Replies: 3
    Last Post: 07-27-2012, 01:44 PM

Bookmarks

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