Excel solver add - error in calculation with mulitple values

Jesper Funch

New Member
Sep 9, 2014
I'm having problems with running Excel solver.

I am currently working with an rather large VBA "project", a project that just got handed over to me, so I have written any of the coding myself, and the original creator is no longer working at my company, so I have no chance of getting help from him. So that is why i'm reaching out to you.

To sketch up what the Excel document is capable of:
It generates slip curves from pressure, temperature and S/C ratio. All these variable are user inputs and there is a userform in which the users can put in the data.
When the user is done putting all the data a Excel solver runs to calculate the values needed for the generation of the slips curves. The slip curves are logarithme curves, reason for this is that negativ results (amount of different chemicals) cannot be negativ.

So if I want to generate a slip curve over the following values: Pressure - 15 kg/cm² ; Temperature - 700°C to 900°C ; S/C ratio 1,8.
No problems. The solver gives me the correct values and the curve is generated correctly.

However! If I want to generate 3 different slip curves (The number of pressure inputs defines the number of slip curves generated) and error in solver shows up.

For an example: 3 different pressures - 5, 10 and 15 kg/cm². The solver now gives negativ values for the slip curves 15 kg/cm², where as before, with only one pressure input (15 kg/cm²) the output was positive and correct.

The coding for the solver is as follows:

Sub rorreformer()
'Finder en løsning'
SolverOptions MaxTime:=100, Iterations:=1000, Precision:=0.0000000001, AssumeLinear:= _​
False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=2, _​
IntTolerance:=0.0000001, Scaling:=False, Convergence:=0.0000000001, AssumeNonNeg:=False​

solveradd cellref:="$G$" & CStr(CO2row), relation:=3, formulatext:="R141C8"
solveradd cellref:="$G$" & CStr(CO2row), relation:=1, formulatext:="100"
solveradd cellref:="$G$" & CStr(COrow), relation:=3, formulatext:="0"
solveradd cellref:="$G$" & CStr(COrow), relation:=1, formulatext:="100"
solveradd cellref:="$G$" & CStr(H2row), relation:=3, formulatext:="0"
solveradd cellref:="$G$" & CStr(H2row), relation:=1, formulatext:="100"
solveradd cellref:="$G$" & CStr(Inertsrow), relation:=3, formulatext:="0"
solveradd cellref:="$G$" & CStr(Inertsrow), relation:=1, formulatext:="100"
solveradd cellref:="$G$" & CStr(CH4row), relation:=3, formulatext:="0"
solveradd cellref:="$G$" & CStr(CH4row), relation:=1, formulatext:="100"
solveradd cellref:="$G$" & CStr(H2Orow), relation:=3, formulatext:="0"
solveradd cellref:="$G$" & CStr(H2Orow), relation:=1, formulatext:="100"
solveradd cellref:="$D$" & CStr(X2row), relation:=3, formulatext:="R141C8"
SolverOk SetCell:="$D$" & CStr(Controlrow), MaxMinVal:=3, ValueOf:="0", _
bychange:="$D$" & CStr(X1row) & ":$D$" & CStr(X2row)
solversolve (True)

End Sub

There are three multiple solvers running. The values the solver refereres to are "inchangeable" and should not be the problem.

Please let me know if you need any more VBA code or Excel sheets.

Best Regards
Jesper Poulsen

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back