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

- 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()
Application.EnableEvents = False
SolverOk SetCell:="\$T\$24", MaxMinVal:=3, ValueOf:=0, ByChange:= _
SolverOk SetCell:="\$T\$24", MaxMinVal:=3, ValueOf:=0, ByChange:= _
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.

Best regards.

#### Attached Files:

• ###### Chimica_calcoli_automatici_rev05.xlsm
File size:
274.9 KB
Views:
5
Last edited by a moderator: Dec 6, 2017
2. ### ChihiroExcel Ninja

Messages:
4,826
Khalid NGO likes this.
3. ### HuiExcel NinjaStaff Member

Messages:
11,485
"YES IT DOES"

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

Try this:
4. ### HasherNew 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.
5. ### HasherNew Member

Messages:
20
Code (vb):

Sub RisDiffZero()
Application.EnableEvents = False
SolverOk SetCell:="\$T\$24", MaxMinVal:=2, ValueOf:=0, ByChange:= _
SolverOk SetCell:="\$T\$24", MaxMinVal:=2, ValueOf:=0, ByChange:= _
Application.EnableEvents = True
End Sub

6. ### HasherNew 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.

File size:
274.7 KB
Views:
5
7. ### HuiExcel NinjaStaff Member

Messages:
11,485
It worked for me with the original data?
8. ### HuiExcel NinjaStaff Member

Messages:
11,485

Note the highlighted duplicate constraints above?

This works for me when I change the input cells
9. ### HuiExcel NinjaStaff Member

Messages:
11,485
I'd use code like:

Code (vb):

Sub Solver_Code()
'
' Hui 2017
'
Application.EnableEvents = False

SolverReset 'Clears all Solver options

SolverOk SetCell:="\$T\$24", MaxMinVal:=3, ValueOf:=0, ByChange:= _

SolverSolve 'Solve

Application.EnableEvents = True
End Sub

10. ### HasherNew 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. ### HasherNew 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. ### HuiExcel NinjaStaff Member

Messages:
11,485
Try this:

see attached file:

File size:
250.7 KB
Views:
3
13. ### HasherNew 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. ### HuiExcel NinjaStaff Member

Messages:
11,485
When I run the Solver I get Integers in the 4 cells?
15. ### HasherNew 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.