Problem with solver constraints

[reeee]

Can anyone help with this solver VBA code? basically it refuses to set the constraint for cells(106,3+chkcount) to 1
****solveradd cellref:=Sheet1.Cells(106, 3 + chkcount), relation:=3, formulatext:=1***

If I change this to zero it works but not 1... Doesn anyone know why? I thought it was becasue the cells were zero and that would make it illegal so I changed em to one before the line is done but still it doesn't add the constraint.



If CheckBox1.Value = True Then
total = TextBox1
Sheet1.Cells(116, 3) = TextBox1
For chkcount = 1 To Sheet2.Cells(20, 9)
Sheet1.Cells(106, 3 + chkcount) = 1
solveradd cellref:=Sheet1.Cells(106, 3 + chkcount), relation:=3, formulatext:=1

If chkcount = 1 Then
solveradd cellref:=Sheet1.Cells(106, 3 + chkcount), relation:=1, formulatext:=100 / TextBox1
'solverchange cellref:=Sheet1.Cells(106, 3 + chkcount), relation:=1, formulatext:=100 / TextBox1
Else
solveradd cellref:=Sheet1.Cells(106, 3 + chkcount), relation:=1, formulatext:=Sheet1.Cells(106, 3 + chkcount - 1)
solverchange cellref:=Sheet1.Cells(106, 3 + chkcount), relation:=1, formulatext:=Sheet1.Cells(106, 3 + chkcount - 1)
End If



Next chkcount
solveradd cellref:=Sheet1.Cells(106, 3), relation:=3, formulatext:=0
'solverchange cellref:=Sheet1.Cells(106, 3), relation:=3, formulatext:=0

 

[joerd]

Please read thread770-199259 and see if it answers your query. The Solver sometimes messes up with local and global names in the spreadsheet, and that may be what you have, too.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[reeee]

I don't understand what is actually being done with the code shown, are you saying that solver gets confused between sheets?

 

[reeee]

OK I have re-done my code, Im sorry but I didn't understand what was happening with what was suggested by yourself on the other page, this is my new code. It works perfectly but for some reason when I make a few changes to the cells within this same segemnt of code, solver just sits there but reports that everythin is "optimal" also the solver reset doesn't seem to be working propperly at the end. My brain is really starting to hurt now!

Cells(106, 3) = TextBox2
yearcount = TextBox2
preasureyr = "R111C12:R111C" & 11 + TextBox2
totalyrs = "R111C11:R111C" & 11 + (TextBox2 * 2)

SolverOk SetCell:="R108C34", MaxMinVal:=2, ValueOf:="0", ByChange:=totalyrs
SolverAdd CellRef:="R111C11", Relation:=3, FormulaText:="0"

SolverOk SetCell:="R108C34", MaxMinVal:=2, ValueOf:="0", ByChange:=totalyrs
SolverAdd CellRef:=preasureyr, Relation:=1, FormulaText:="R32C3"

SolverOk SetCell:="R108C34", MaxMinVal:=2, ValueOf:="0", ByChange:=totalyrs


SolverSolve UserFinish:=False
Sheets("Sheet2").Select
Application.ScreenUpdating = True
solverreset