Problem with solver constraints
Problem with solver constraints
(OP)
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
****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





RE: Problem with solver constraints
Cheers,
Joerd
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: Problem with solver constraints
RE: Problem with solver constraints
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