Greg Snidow,

I initially read through your post too quickly and didn't fully address your
question. Try using the range objects listed in my initial post and alter
them accordingly for each of the three constraints. See if this will solve
the issue, or at least help point out where the problem resides.

Best,

Matthew Herbert

Dim Wks As Worksheet
Dim rngCellRef As Range
Dim intRelation As Integer
Dim rngFormulaText As Range

Set Wks = Sheets("MacroDEA")

'-------------------------------------------------------------------
'Constraint 1
With Wks
Set rngCellRef = Range(.Cells(2, ThetaCol + 1), _
.Cells(LastInputRow, ThetaCol + 1))
Set rngFormulaText = Range(.Cells(2, ThetaCol + 3), _
.Cells(LastInputRow, ThetaCol + 3))
End With

intRelation = 1

SolverAdd CellRef:=rngCellRef, _
Relation:=intRelation, _
FormulaText:=rngFormulaText
'-------------------------------------------------------------------

'-------------------------------------------------------------------
'Constraint 2
'Add accordingly
'-------------------------------------------------------------------

'-------------------------------------------------------------------
'Constraint 3
With Wks
Set rngCellRef = .Cells(LastDataRow + 1, ThetaCol + 1)

'uncomment this and change if the FormulaText references a range
'Set rngFormulaText = .Cells(Row #, Column #)
End With

intRelation = 2

'add in rngFormulaText if uncommented from above
SolverAdd CellRef:=rngCellRef, _
Relation:=intRelation, _
FormulaText:=1
'-------------------------------------------------------------------

"Matthew Herbert" wrote:

Greg Snidow,

Ultimately, it looks like your range objects are missing a final ")". I've
provided a slightly different approach below. It's not tested, so if Solver
wants a string input, i.e. .Address, as opposed to a Range Object, then
simply tack a .Address on the end of the range objects.

Best,

Matthew Herbert

Dim Wks As Worksheet
Dim rngCellRef As Range
Dim intRelation As Integer
Dim rngFormulaText As Range

Set Wks = Sheets("MacroDEA")

With Wks
Set rngCellRef = Range(.Cells(2, ThetaCol + 1), _
.Cells(LastInputRow, ThetaCol + 1))
Set rngFormulaText = Range(.Cells(2, ThetaCol + 3), _
.Cells(LastInputRow, ThetaCol + 3))
End With

intRelation = 1

SolverAdd CellRef:=rngCellRef, _
Relation:=intRelation, _
FormulaText:=rngFormulaText


"Greg Snidow" wrote:

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