+ Reply to Thread
Results 1 to 4 of 4

[SOLVED] Missing condition after solver called by macro

  1. #1

    [SOLVED] Missing condition after solver called by macro

    Hi there

    I am trying to run the solver by using macro code. So far the solver is
    reacting and running properly (with the reference "Tools -> References
    -> Solver" activated), except one thing:

    After running the macro, I invoke the solver manually to check if the
    macro has set the correct solver settings. All is fine, but one
    condition is missing: Condition (1) is fine (restricting E9 to positive
    values), but condition (2) (restricting E9 to values less than 1) is

    Any idea why? - Help greatly appreciated

    samkut @ web.de

    Sub solver_macro()
    SolverAdd CellRef:="$E$9", Relation:=3, FormulaText:="0"
    SolverAdd CellRef:="$E$9", Relation:=1, FormulaText:="1"
    SolverOk SetCell:="$F$4", MaxMinVal:=2, ValueOf:="0", ByChange:= _
    SolverSolve UserFinish:=True
    End Sub

  2. #2
    Registered User
    Join Date

    Angry Me too!

    I'm having a very similar problem as Sam. I have four constraints, only three of which are being respected. The fourth is to keep a number <= 5. It's not syntax because of several reasons:
    - I've rearranged the code = No Good
    - I've copied and pasted knows good constraints = No Good
    - I've recorded a new macro and copied/pasted = No Good
    - Some numbers work:
    4.9, 5.1
    (any number w/ decimal)
    (any negative number)
    15, 16, 100
    (any number > 14)

    Here are the numbers that don't work:
    1 - 14
    (any positive integer less than 15)

    Here's my code:
    ' Select formula worksheet and reset initial state
    ActiveCell.FormulaR1C1 = "0"
    ActiveCell.FormulaR1C1 = "0"
    ActiveCell.FormulaR1C1 = "0"
    SolverOptions Iterations:=100000

    ' Set solver target and manipulation cells
    SolverOk SetCell:="$O$37", MaxMinVal:=2, ByChange:="$D$27:$D$29"

    ' Add formula/result constraints (Offset adjustment limits)
    SolverAdd CellRef:=Range("$J$45:$J$46"), Relation:=1, FormulaText:="0.7" ' Shift Upper Limit
    SolverAdd CellRef:=Range("$J$45:$J$46"), Relation:=3, FormulaText:="-0.7" ' Shift Lower Limit
    SolverAdd CellRef:="$J$47", Relation:=1, FormulaText:="5" ' 2FE Upper Limit
    SolverAdd CellRef:="$J$47", Relation:=3, FormulaText:="-5" ' 2FE Lower Limit

    ' Compute Solver solution, accept results, return to results worksheet
    SolverSolve UserFinish:=False ' [DELETE THIS FOR FINAL]
    ' SolverSolve UserFinish:=True [USE THIS FOR FINAL]
    Sheets("Limits Report 1").Select ' [DELETE THIS FOR FINAL]
    ' Sheets("13point - FINE").Select [USE THIS FOR FINAL]

  3. #3
    Registered User
    Join Date
    Any suggestions?!

  4. #4
    Registered User
    Join Date

    Cool How to fix missing conditions when calling Solver using a macro

    Sam solved our problems. Apparently, some graphics/pictures/graphs will cause the macro to ignore solver conditions involving positive integers less than 15. If that happens to you, delete any pictures/graphs one by one until your macro works. Once you have your picture(s) isolated, then you should be able to delete only the problem picture(s) and leave all the rest.

    If you can't live without the problem picture(s), the only other option is to change the number. For example, if your limit is supposed to be 5, instead use 4.9999999

+ Reply to Thread

Thread Information

Users Browsing this Thread

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