SOLVER DOESN T WORK!

[Hasher]

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.

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.

 

[Chihiro]

Hi Please read the link.
https://chandoo.org/forum/threads/new-users-please-read.294/

Specifically.

• PLEASE DON'T SHOUT! We have big ears and will hear you just the same.

Meaning please don't use all caps (be it in title or in the body).

 

[Hui]

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:

 

[Hasher]

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:

 

[Hasher]

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

 

[Hasher]

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.

 

[Hui]

It worked for me with the original data?

 

[Hui]

Your original Solver setup as downloaded?


Note the highlighted duplicate constraints above?


This works for me when I change the input cells

 

[Hui]

I'd use code like:

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

 

[Hasher]

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

 

[Hasher]

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?

 

[Hui]

Try this:

see attached file:

 

[Hasher]

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..

 

[Hui]

When I run the Solver I get Integers in the 4 cells?

 

[Hasher]

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

 

[Hasher]

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