Excel Solver in VB
 

I am trying to run this piece of code but I am getting nothing but the first
constraint in the Solver. I'd be happy is someone could help me with this.
Thanks..

Public Sub DualSolver(K, M, N)
'Activate the DLP sheet
Sheets("DLP").Activate

'Reset the solver
SolverReset

'Objective Function
SolverOk SetCell:=Range("B4"), MaxMinVal:=1, ValueOf:="0",
ByChange:="Range(Cells(2, 2), Cells(2, M + N +
1)),Range(Cells(1,M+N+6),Cells(K,M+N+6))"

'Constraints

SolverAdd CellRef:=Cells(K + 1, M + N + 6), Relation:=2, FormulaText:="1"
SolverAdd CellRef:=Range(Cells(6, 2), Cells(6, M + N + 1)), Relation:=2,
FormulaText:="Range(Cells(8, 2), Cells(8, M + N + 1))"


'Solver Options
SolverOptions MaxTime:=100, Iterations:=10000, Precision:=0.000001,
AssumeLinear _
:=True, StepThru:=False, Estimates:=1, Derivatives:=1,
SearchOption:=1, _
IntTolerance:=5, Scaling:=False, Convergence:=0.0001,
AssumeNonNeg:=True

ActiveWorkbook.Worksheets("DLP").Cells(100, 100).Formula = 1 / 0
'Solve
SolverSolve UserFinish:=True


'Finish and discard
SolverFinish KeepFinal:=2
End Sub

Excel Solver in VB
 

Try putting a line continuation character (underscore) at the end of
the first of these lines:
SolverAdd CellRef:=Range(Cells(6, 2), Cells(6, M + N + 1)), Relation:=2,
FormulaText:="Range(Cells(8, 2), Cells(8, M + N + 1))"

Hth,
Merjet

Excel Solver in VB
 

Thanks but it didn't help...

"merjet" wrote:

Try putting a line continuation character (underscore) at the end of
the first of these lines:
SolverAdd CellRef:=Range(Cells(6, 2), Cells(6, M + N + 1)), Relation:=2,
FormulaText:="Range(Cells(8, 2), Cells(8, M + N + 1))"

Hth,
Merjet

Excel Solver in VB
 

Thank you very much Dana. With this I managed to get the objective function
in the solver but I still can not get the second constraint which is:

SolverAdd CellRef:=Range(Cells(6, 2), Cells(6, M + N + 1)), Relation:=2,
FormulaText:="Range(Cells(8, 2),Cells(8, M + N + 1))"

pLEASE discard ActiveWorkbook.Worksheets("DLP").Cells(100, 100).Formula = 1
/ 0
I added this piece in order to understand where my code stuck!

Thank you

"Dana DeLouis" wrote:

Not sure, but with multiple ranges, you may want to use Union.
I can't tell from the variables, but there could be a chance that the second group overlaps the first group on the second row.

Set Rng = Union(Range(Cells(2, 2), Cells(2, M + N + 1)), Range(Cells(1, M + N + 6), Cells(K, M + N + 6)))
SolverOk SetCell:=Range("B4"), MaxMinVal:=1, ByChange:=Rng.Address

I don't know what this does:
ActiveWorkbook.Worksheets("DLP").Cells(100, 100).Formula = 1 / 0

It usually is
.
ActiveWorkbook.Worksheets("DLP").Cells(100, 100).Formula = "=1/0"

--
HTH :)
Dana DeLouis


"Ays" wrote in message ...
I am trying to run this piece of code but I am getting nothing but the first
constraint in the Solver. I'd be happy is someone could help me with this.
Thanks..

Public Sub DualSolver(K, M, N)
'Activate the DLP sheet
Sheets("DLP").Activate

'Reset the solver
SolverReset

'Objective Function
SolverOk SetCell:=Range("B4"), MaxMinVal:=1, ValueOf:="0",
ByChange:="Range(Cells(2, 2), Cells(2, M + N +
1)),Range(Cells(1,M+N+6),Cells(K,M+N+6))"

'Constraints

SolverAdd CellRef:=Cells(K + 1, M + N + 6), Relation:=2, FormulaText:="1"
SolverAdd CellRef:=Range(Cells(6, 2), Cells(6, M + N + 1)), Relation:=2,
FormulaText:="Range(Cells(8, 2), Cells(8, M + N + 1))"


'Solver Options
SolverOptions MaxTime:=100, Iterations:=10000, Precision:=0.000001,
AssumeLinear _
:=True, StepThru:=False, Estimates:=1, Derivatives:=1,
SearchOption:=1, _
IntTolerance:=5, Scaling:=False, Convergence:=0.0001,
AssumeNonNeg:=True

ActiveWorkbook.Worksheets("DLP").Cells(100, 100).Formula = 1 / 0
'Solve
SolverSolve UserFinish:=True


'Finish and discard
SolverFinish KeepFinal:=2
End Sub

Excel Solver in VB
 

I got rid of the "" in the second constraint and it worked...

Thank you very much for your time..

"Dana DeLouis" wrote:

... I still can not get the second constraint

Hi. In the big picture, you are probably adding something that doesn't
make sense to Solver.
Solver will not indicate a logic error at this time when loaded via vba.
In general, it will just not add the constraint.
Another problem in general with this code is that we do not know what the
variables m,n,& k are.
The adjustable cells could overlap depending on what k is. If someone
entered an incorrect value, the adjustable cells could point to a cell
that contains Text.
Solver won't flag an error at this time in vba...in general, it will just
skip it.
I usually run a program that uses a standard color scheme for Solver to
make it easier to visualize.
Maybe you can do something similar here for debugging.
Note that you can drop "Value Of" in a Max problem.
Again, when using variables, you need to make sure they point to valid
cells.
Here are some general ideas:

Dim NC As Long 'Number of Columns M+N
NC = M + N

'Reset the solver
SolverReset

'Make you complicated ranges here so that you can test them:
Set rng1 = Union(Range(Cells(2, 2), Cells(2, NC + 1)), Range(Cells(1, NC +
6), Cells(K, NC + 6)))
Set rng2 = Cells(K + 1, NC + 6)
Set rng3 = Range(Cells(6, 2), Cells(6, NC + 1))
Set rng4 = Range(Cells(8, 2), Cells(8, NC + 1))

'Test what you have:
rng1.Interior.Color = vbRed
rng2.Interior.Color = vbBlue
rng3.Interior.Color = vbYellow
rng4.Interior.Color = vbGreen

'Stop here, and check your workbook...

'Objective Function
SolverOk SetCell:=Range("B4"), MaxMinVal:=1, ByChange:=rng1.Address

'Constraints
SolverAdd CellRef:=rng2, Relation:=2, FormulaText:="1"
SolverAdd CellRef:=rng3, Relation:=2, FormulaText:=rng4.Address

--
HTH :)
Dana DeLouis


"Ays" wrote in message
...
Thank you very much Dana. With this I managed to get the objective
function
in the solver but I still can not get the second constraint which is:

SolverAdd CellRef:=Range(Cells(6, 2), Cells(6, M + N + 1)), Relation:=2,
FormulaText:="Range(Cells(8, 2),Cells(8, M + N + 1))"

pLEASE discard ActiveWorkbook.Worksheets("DLP").Cells(100, 100).Formula
= 1
/ 0
I added this piece in order to understand where my code stuck!

Thank you

"Dana DeLouis" wrote:

Not sure, but with multiple ranges, you may want to use Union.
I can't tell from the variables, but there could be a chance that the
second group overlaps the first group on the second row.

Set Rng = Union(Range(Cells(2, 2), Cells(2, M + N + 1)), Range(Cells(1,
M + N + 6), Cells(K, M + N + 6)))
SolverOk SetCell:=Range("B4"), MaxMinVal:=1, ByChange:=Rng.Address

I don't know what this does:
ActiveWorkbook.Worksheets("DLP").Cells(100, 100).Formula = 1 / 0

It usually is
.
ActiveWorkbook.Worksheets("DLP").Cells(100, 100).Formula = "=1/0"

--
HTH :)
Dana DeLouis


"Ays" wrote in message
...
I am trying to run this piece of code but I am getting nothing but the
first
constraint in the Solver. I'd be happy is someone could help me with
this.
Thanks..

Public Sub DualSolver(K, M, N)
'Activate the DLP sheet
Sheets("DLP").Activate

'Reset the solver
SolverReset

'Objective Function
SolverOk SetCell:=Range("B4"), MaxMinVal:=1, ValueOf:="0",
ByChange:="Range(Cells(2, 2), Cells(2, M + N +
1)),Range(Cells(1,M+N+6),Cells(K,M+N+6))"

'Constraints

SolverAdd CellRef:=Cells(K + 1, M + N + 6), Relation:=2,
FormulaText:="1"
SolverAdd CellRef:=Range(Cells(6, 2), Cells(6, M + N + 1)),
Relation:=2,
FormulaText:="Range(Cells(8, 2), Cells(8, M + N + 1))"


'Solver Options
SolverOptions MaxTime:=100, Iterations:=10000, Precision:=0.000001,
AssumeLinear _
:=True, StepThru:=False, Estimates:=1, Derivatives:=1,
SearchOption:=1, _
IntTolerance:=5, Scaling:=False, Convergence:=0.0001,
AssumeNonNeg:=True

ActiveWorkbook.Worksheets("DLP").Cells(100, 100).Formula = 1 / 0
'Solve
SolverSolve UserFinish:=True


'Finish and discard
SolverFinish KeepFinal:=2
End Sub