Set solver constraint not working in VB
Greg,
When you use the With Statement, ensure that you qualify correctly. I'll
illustrate with a simply example below. (See the explanation after the code).
Sub QualifyRanges()
Dim wksOne As Worksheet
Dim wksTwo As Worksheet
Dim Rng As Range
Set wksOne = Worksheets("Sheet1")
Set wksTwo = Worksheets("Sheet2")
wksOne.Select
With wksTwo
.Range("A1").Value = "This is worksheet two."
Set Rng = Range("A1:B5")
MsgBox Rng.Address(External:=True)
.Select
End With
End Sub
The main point here is that the Rng Object is referring to Sheet1 (i.e. the
current ActiveSheet by default), not Sheet2.
The Set statement creates an object. So, in the code above I created three
objects wksOne, wksTwo, and Rng. wksOne and wksTwo are Worksheet objects
and Rng is a Range object. Thus, wksOne and wksTwo take on all the
attributes available to Worksheets and Rng takes on all the attributes
available to Range. Cool, huh? The With statement allows you to run a
series of statements for one object. So, With the wksTwo object, perform all
the . attributes (i.e. you get the benefit of Intellisence popping up when
you type the dot operator). For example, its as if the .Select in the With
statement were wksTwo.Select. With statements help prevent errors, decrease
the amount of typing that is needed, and help with code compilation; however,
you fall into the potential trap of not applying a . appropriately, like
with the Set Rng line of code above. The Set Rng line of code is missing the
. before Range("A1:B5"). Thus, when the program gets to this line of code,
the default hierarchy is the active workbook and the active worksheet. Even
though the Set Rng statement is embedded in the With statement, Rng is set to
a range on Sheet1, not Sheet2. Put the . before Range("A1:B5") and rerun
the code. (The External parameter of the Address property returns the fully
qualified address of the range). Below are the message box results of the
two scenarios:
- Set Rng = Range("A1:B5"): [Book1]Sheet1!$A$1:$B$5
- Set Rng = .Range("A1:B5"): [Book1]Sheet2!$A$1:$B$5
Notice the difference in Sheet1! and Sheet2!
Best,
Matt
"Greg Snidow" wrote:
Mathew, thank you for your suggestions. I am almost ready to give it a go,
but I wanted to throw something out there. I ended up reversing my data set
up, which meant I had to slightly alter almost 1000 lines of code, so that
the constraint ranges would not change, so I no longer need variables for
them in the Solver part. I thought I was good to go until I tried it. Now,
with the hard coded constraint ranges, all three constraints are added with
no problems. However, now the *same* variable I *was* using for the ByChange
range no longer works, and it only works if I hard code *it*. I will let you
know how it comes out. I'm just learning how to use range variable in a With
block, so it might take me a little while. Thanks again.
Greg
"Matthew Herbert" wrote:
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
|