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