+ Reply to Thread
Results 1 to 2 of 2

Limit Goal Seek to only searching for certain values in VBA

  1. #1
    Registered User
    Join Date
    07-15-2020
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    9

    Limit Goal Seek to only searching for certain values in VBA

    I'm trying to automate a calculation using Goal Seek function via VBA, which is trying to change Diameterindv/P/c16 until it makes QFuld/R/c18 larger than Q Max/N/c14. R is a formular that calculates the flow based on the diameter in P. This must be larger than the nescesarry incoming flow in N.

    I've added a screenshot and a bit of code. However when running the below code, I often get weird results (large negative numbers among them). Example is when running the code P is suddenly -63082144,16.

    I'm wondering if it's possible for Goal Seek to only seek in prespecified numbers/range? For instance, I only want the numbers it can use as a solution to be 188, 235, 297, 377, 500, 600.

    Current wrong result:
    RfAaJ.png

    Desired result in red:
    jE7ig.png


    Please Login or Register  to view this content.
    I've added a workbook, in the Workbook it's the button "Beregn Indv", that runs Module 3.
    Attached Files Attached Files

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

    Re: Limit Goal Seek to only searching for certain values in VBA

    I'm wondering if it's possible for Goal Seek to only seek in prespecified numbers/range? For instance, I only want the numbers it can use as a solution to be 188, 235, 297, 377, 500, 600.
    I expect that it is "possible", but limiting the solution set to a handful of specific values the way you have done it makes column R a step function, and goal seek (and other numeric algorithms based a Newton-Raphson type of algorithm) really do not like step functions. I would probably approach this in one of two ways:
    Approach A: Solve the unrestricted problem, then choose the diameter based on that solution. Change column R so that it refers to column P instead of column Q, run your goal seek procedure, then read off the result in column Q.
    Approach B: you have only 6 allowed values, abandon goal seek and brute force try the 6 values. Choose the result you want from the 6 trials.

    I'm not sure how open you are to doing this differently, so I haven't filled in details. I think I would approach this differently. Let me know how you want to proceed.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ 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. [SOLVED] Macro for multiple values within a Goal Seek
    By clogistics in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 03-03-2020, 09:03 PM
  2. Limit the maximum value of variable related to Goal Seek Excel VBA
    By Sam1989 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-03-2019, 04:03 PM
  3. Finding values / goal seek
    By DMueller in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-21-2013, 07:20 PM
  4. Run goal seek if values are changed and avoiding unlimited loops
    By Operator in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-25-2009, 10:56 AM
  5. populate cells in a matrix from goal seek values based on
    By W2009 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 08-08-2009, 02:12 AM
  6. Replies: 1
    Last Post: 01-27-2006, 12:00 AM
  7. how to change values to formula in VBA to carry out goal seek?
    By Desmond in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-18-2006, 08:35 AM

Tags for this Thread

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