G
Greg Snidow
Greetings all. I've got a solver macro, which successfully sets two of three
required constraints by using variable ranges in the SolverAdd statement,
like bellow, where LastInputRow and ThetaCol are integer variables.
' Set the input range constraint
SolverAdd CellRef:=Sheets("MacroDEA").Range(Sheets("MacroDEA") _
.Cells(2, ThetaCol + 1).Address & ":" & _
Sheets("MacroDEA").Cells(LastInputRow, ThetaCol + 1) _
.Address), Relation:=1, formulatext:= _
Sheets("MacroDEA").Range(Sheets("MacroDEA") _
.Cells(2, ThetaCol + 3).Address & ":" & _
Sheets("MacroDEA").Cells(LastInputRow, ThetaCol +
3).Address)
The above code is equivalent to...
SolverAdd CellRef:="$BV$2:$BV$6", Relation:=1, formulatext:="$BX$2:$BX$6",
and it works without a hitch.
I have two range constraints set up like this, and both are added to solver
no problem. However, I also have a third constraint that is only one cell,
and I need to also use variable cell ranges. The below is the hard coded
constraint, which is added to the constraints no problem...
SolverAdd CellRef:="$BV$10", Relation:=2, formulatext:="$BX$10"
When I try to use the same set up for the third constraint, it does not
work, like this...
SolverAdd CellRef:=Sheets("MacroDEA").Cells(LastDataRow + 1, ThetaCol + 1), _
Relation:=2, formulatext:=1
I tried naming the cell's address as a variable and using the variable, but
that did not work either. The only thing I can do to make it stick is hard
code it, which will not work for my set up. I am completely stumped over
this third constraint. Any ideas? Thank you
Greg Snidow
required constraints by using variable ranges in the SolverAdd statement,
like bellow, where LastInputRow and ThetaCol are integer variables.
' Set the input range constraint
SolverAdd CellRef:=Sheets("MacroDEA").Range(Sheets("MacroDEA") _
.Cells(2, ThetaCol + 1).Address & ":" & _
Sheets("MacroDEA").Cells(LastInputRow, ThetaCol + 1) _
.Address), Relation:=1, formulatext:= _
Sheets("MacroDEA").Range(Sheets("MacroDEA") _
.Cells(2, ThetaCol + 3).Address & ":" & _
Sheets("MacroDEA").Cells(LastInputRow, ThetaCol +
3).Address)
The above code is equivalent to...
SolverAdd CellRef:="$BV$2:$BV$6", Relation:=1, formulatext:="$BX$2:$BX$6",
and it works without a hitch.
I have two range constraints set up like this, and both are added to solver
no problem. However, I also have a third constraint that is only one cell,
and I need to also use variable cell ranges. The below is the hard coded
constraint, which is added to the constraints no problem...
SolverAdd CellRef:="$BV$10", Relation:=2, formulatext:="$BX$10"
When I try to use the same set up for the third constraint, it does not
work, like this...
SolverAdd CellRef:=Sheets("MacroDEA").Cells(LastDataRow + 1, ThetaCol + 1), _
Relation:=2, formulatext:=1
I tried naming the cell's address as a variable and using the variable, but
that did not work either. The only thing I can do to make it stick is hard
code it, which will not work for my set up. I am completely stumped over
this third constraint. Any ideas? Thank you
Greg Snidow