1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SOLVER DOESN T WORK!

Discussion in 'VBA Macros' started by Hasher, Dec 5, 2017.

  1. Hasher

    Hasher New Member

    Messages:
    20
    Hi of all.
    The file that I attached, it is write in italian language.

    The part made in excel is compounded by:

    - Five button

    - 2 drop down menu

    - the central part that you see colored in green is for showing the result of calculation

    - the last part that is the result of reaction.

    The cells B9 and L9 is used for calculating the number of atoms in each chemical element that made part of reagents (left side of arrow) you can find the results of this, in cells V10; X10; Z10; AB10
    The cells AD9 and AO9 is used for calculating the number of atoms in each chemical element that do part of products (right side of arrow) you can find the results of this, in cells V8; X8; Z8; AB8.
    Either numbers of atom that compound the reagents and numbers of atom that compound the products are already calculated in the cells above.

    What would i like to do?
    I would like that the differences between the follow cells is 0, so:

    V8 - V10 or V10 - V8 = 0

    X8 - X10 or X10 - X8 = 0

    Z8 - Z10 or Z10 - Z8 = 0

    AB8 - AB10 or AB10 - AB8 = 0

    these differents has already been calculated in cells V6, X6, Z6, AB6 then i have calculated the differents between all these cells with a formula in cell T24 (V6+X6+Z6+AB6). This is my target cell in my solver.
    This cell must become 0 when i will use the solver.
    The constrains are that the cells B9, L9, AD9, AO9 must be greater than 0 and their must be whole numbers (it must be natural numbers).
    But it doesn't work.

    I also tried to use the solver+VBA code but the result were bad...
    So i ask you an help for solving this problem.
    It follow below the solver's code.

    Code (vb):
    Sub RisDiffZero()
        Dim answer As Integer
        Application.EnableEvents = False
        SolverOk SetCell:="$T$24", MaxMinVal:=3, ValueOf:=0, ByChange:= _
            "$B$9,$L$9,$AD$9,$AO$9", Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverAdd CellRef:="$B$9", Relation:=1, FormulaText:="Variant"
        SolverAdd CellRef:="$L$9", Relation:=1, FormulaText:="Variant"
        SolverAdd CellRef:="$AD$9", Relation:=1, FormulaText:="Variant"
        SolverAdd CellRef:="$AO$9", Relation:=1, FormulaText:="Variant"
        SolverOk SetCell:="$T$24", MaxMinVal:=3, ValueOf:=0, ByChange:= _
            "$B$9,$L$9,$AD$9,$AO$9", Engine:=1, EngineDesc:="GRG Nonlinear"
            answer = SolverSolve(True, "ShowTrial")
        ShowTrial answer
        answer = SolverSolve(True, "ShowTrial")
        Application.EnableEvents = True
    End Sub
     
    I call this code when i change something in drop down menĂ¹.
    I attach my file and i hope that somebody can help me.

    Thank you in advance.
    Best regards.

    Attached Files:

    Last edited by a moderator: Dec 6, 2017
  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,917
    Khalid NGO likes this.
  3. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,836
    I was going to answer
    "YES IT DOES"

    But it only works when you have configured it correctly
    I think you have over configured the constraints

    Try this:
    upload_2017-12-6_11-7-49.png
  4. Hasher

    Hasher New Member

    Messages:
    20
    Hi Hui.
    Firstly, i want to thank you for your help.
    Unfortunately, it doesn't work like i would.
    Because, it has to work without to give me decimal numbers and if i put like constraints (FormulaText:="Integer") it give me these results.
    Anyway i have tried to do something changing in solver's code.
    I have put like target that the cell T24 give me a minimun result and i have left the constrains like before.
    But, it sometimes works well and sometimes works bad.
    I hope that somebody can help to solve these problems.
    Follow below new code about solver:
  5. Hasher

    Hasher New Member

    Messages:
    20
    Code (vb):

    Sub RisDiffZero()
        Dim answer As Integer
        Application.EnableEvents = False
        SolverOk SetCell:="$T$24", MaxMinVal:=2, ValueOf:=0, ByChange:= _
            "$B$9,$L$9,$AD$9,$AO$9", Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverAdd CellRef:="$B$9", Relation:=1, FormulaText:="Variant"
        SolverAdd CellRef:="$L$9", Relation:=1, FormulaText:="Variant"
        SolverAdd CellRef:="$AD$9", Relation:=1, FormulaText:="Variant"
        SolverAdd CellRef:="$AO$9", Relation:=1, FormulaText:="Variant"
        SolverOk SetCell:="$T$24", MaxMinVal:=2, ValueOf:=0, ByChange:= _
            "$B$9,$L$9,$AD$9,$AO$9", Engine:=1, EngineDesc:="GRG Nonlinear"
            answer = SolverSolve(True, "ShowTrial")
        ShowTrial answer
        answer = SolverSolve(True, "ShowTrial")
        Application.EnableEvents = True
    End Sub
     
  6. Hasher

    Hasher New Member

    Messages:
    20
    I choose to use EngineDesc:="Evolution", and it works well.
    But it remains a problem.
    It just works well when the target cell (T24) is already = 0.
    While, if the result of the equation before is <> 0. It works bad.
    I don't understand why does it happens?
    I attach my new file where you can see it.
    I think that it is the last step that we have to solve.
    Thank you in advance.

    Attached Files:

  7. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,836
    It worked for me with the original data?
  8. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,836
    Your original Solver setup as downloaded?
    upload_2017-12-6_16-50-9.png

    Note the highlighted duplicate constraints above?


    This works for me when I change the input cells
    upload_2017-12-6_16-48-3.png
  9. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,836
    I'd use code like:

    Code (vb):

    Sub Solver_Code()
    '
    ' Hui 2017
    '
     Application.EnableEvents = False
     
      SolverReset 'Clears all Solver options
     
      'Add constraints
     SolverAdd CellRef:="$B$9", Relation:=3, FormulaText:="1"
      SolverAdd CellRef:="$B$9", Relation:=4, FormulaText:="integer"
      SolverAdd CellRef:="$L$9", Relation:=3, FormulaText:="1"
      SolverAdd CellRef:="$L$9", Relation:=4, FormulaText:="integer"
      SolverAdd CellRef:="$AD$9", Relation:=3, FormulaText:="1"
      SolverAdd CellRef:="$AD$9", Relation:=4, FormulaText:="integer"
      SolverAdd CellRef:="$AO$9", Relation:=3, FormulaText:="1"
      SolverAdd CellRef:="$AO$9", Relation:=4, FormulaText:="integer"
     
      'Add other settings
     SolverOk SetCell:="$T$24", MaxMinVal:=3, ValueOf:=0, ByChange:= _
      "$B$9,$L$9,$AD$9,$AO$9", Engine:=1, EngineDesc:="GRG Nonlinear"

      SolverSolve 'Solve

      Application.EnableEvents = True
    End Sub
     
  10. Hasher

    Hasher New Member

    Messages:
    20
    Hi Hui.
    Thank you again for your help.
    Can you attach me your file. So, I can see that it works.
    Because i tried to do like you suggested me, but, it doesn t work again.
    So, i think that maybe i have made something mistakes
  11. Hasher

    Hasher New Member

    Messages:
    20
    Sorry,
    I used your code and it gives me decimal numbers like results.
    It is wrong, because, i would just to have natural numbers.
    So, the question remains:
    why doesn't works when the minimum result (cell T24) is <> 0, while, Why Does it always work well when previous result of the equation was = 0?
  12. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,836
    Try this:
    upload_2017-12-6_17-22-19.png
    see attached file:

    Attached Files:

  13. Hasher

    Hasher New Member

    Messages:
    20
    Sorry, i don't explain me well.
    Natural numbers goes to 1 from x number and they are just whole numbers (1,2,3,4,5...), while, Integer numbers includes decimal numbers and i don't want this result (0,1; 0,2; 0,3; 0,4; 0,5.....1; 1,1; 1,2....).
    I just want natural numbers like results.
    Anyway, thank you for interesting.
    Because you are the one person that take to care for this post..
  14. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,836
    When I run the Solver I get Integers in the 4 cells?
  15. Hasher

    Hasher New Member

    Messages:
    20
    Hi Hui.
    Yes, it is. you can try to change "Integer" to "Variant" in solver's code.
    Then, you try to use and to solve the problems.
    Thank you for your interesting.
    Best regards
  16. Hasher

    Hasher New Member

    Messages:
    20
    I am sorry Hui.
    I don't understand where is my mistake?
    Can you help me?
    Because, i saw that i doen t still works with Acidi + metalli, Anidridi + ossidi and Anidride + Idrossido. So, i thought that i am wrong something and i didn't understand what you said me.
    So, can you explain me better where i wrong.
    Thank you in advance and i hope that you can help me

Share This Page