Using Solver with multiple ranges (Excel 2003 sp2)

K

Kragelund

Hi all,

I am trying to set up a VBA version of a Solver model, which works fine in
"worksheet mode". The Solver has to iterate over two separate ranges. When
using the recorded macro the input values are: ByChange:=
"$H$45:$AG$46,$H$47:$AJ$48". They work fine. When I try to replace the values
with variables, I get an error message, saying something like "global
failed". Can anybody help me with this annoying problem?

Also I'd very much like to know how Solver evaluates whether a given problem
is "too large". I get that message sometimes on the exact same number of
cells which worked just minutes before (in worksheet mode, without VBA).

The dysfunctional code is this:

Sub SolverMakro()

Dim cell1 As Range, cell2 As Range, Cell3 As Range, cell4 As Range, cell5 As
Range

Set cell1 = Range("G27")
Set cell2 = Range("H45:AG46")
'Set Cell3 = Range("AG46")
Set cell4 = Range("H47:AJ48")
'Set cell5 = Range("AJ48")

SOLVER.SolverReset
SOLVER.SolverOk SetCell:=cell1.Address, MaxMinVal:=1, ValueOf:="0",
ByChange:=Range(cell2, cell4).Address

SolverAdd CellRef:=Range(cell2).Address, Relation:=1, FormulaText:="0"
SolverAdd CellRef:=Range(cell2).Address, Relation:=3,
FormulaText:="-42000"
SolverAdd CellRef:=Range(cell4).Address, Relation:=1, FormulaText:="0"
SolverAdd CellRef:=Range(cell4).Address, Relation:=3,
FormulaText:="-42000"

SolverAdd CellRef:="$H$39:$BB$39", Relation:=1, FormulaText:="0"

UserFinish = True
SolverSolve

End Sub

Thanks in advance,

Kragelund
 
T

Tom Ogilvy

To get what you originally showed,

ByChange:=Range(cell2, cell4).Address

would have to be

ByChange:=Union(cell2, cell4).Address

to demonstrate from the immediate window:

set cell2 = Range("$H$45:$AG$46")

set cell4 = Range("$H$47:$AJ$48")
? range(cell2,cell4).Address
$H$45:$AJ$48
? union(cell2, cell4).Address
$H$45:$AG$46,$H$47:$AJ$48

Often annoying problems are self-imposed, although certainly not always and
no guarantee that this is what your current problem is.
 
D

Dana DeLouis

"...The Solver has to iterate over two separate ranges.

Hi. Just guessing. Your "Changing Cells" is one big area...
ByChange:=Range(cell2, cell4).Address
Click to expand...

Debug.Print Range(cell2, cell4).Address
returns:
$H$45:$AJ$48

Is this what you expect?
You may have meant the Union of these two ranges:
Debug.Print Union(cell2, cell4).Address
$H$45:$AG$46,$H$47:$AJ$48
UserFinish = True
SolverSolve
Click to expand...

I believe it should be written as follows:

SolverSolve UserFinish:=True
or just ...
SolverSolve True
SOLVER.SolverReset
SOLVER.SolverOk SetCell:=cell1.Address, MaxMinVal:=1, ValueOf:="0",
Click to expand...

If you wish, you can just use the following...

SolverReset
SolverOk SetCell:=???, MaxMinVal:=1, ByChange:=???

Note that you do not need to include ValueOf = 0 in a Maximization problem,
as it's ignored anyway.

This is not quite how I would do it, but it may give you some ideas of your
own...

Sub DemoSolver()

Dim Target As String
Dim ChgCells As String
Dim Rng1 As Variant
Dim Rng2 As Variant

Set Rng1 = [H45:AG46]
Set Rng2 = [H47:AJ48]

Target = [G27].Address
ChgCells = Union(Rng1, Rng2).Address

'Now Change to Strings...
Rng1 = Rng1.Address
Rng2 = Rng2.Address
' = = = = = = = = = =
SolverReset
SolverOk Target, 1, , ChgCells

SolverAdd Rng1, 1, 0
SolverAdd Rng1, 3, -42000
SolverAdd Rng2, 1, 0
SolverAdd Rng2, 3, -42000

SolverSolve True
End Sub

Hope this helps in some way...
 
D

Dana DeLouis

...or simply control these reports via VBA code.

Hi. Here are just some thoughts...
This is not necessary, but maybe you could calculate the number of columns
as you are, and then do...

NumColumns = 7
Set RngA = CellA_Start.Resize(1, NumColumns)

Here are some ideas...

' Solve w/ no prompts
SolverSolve True
' Keep Final values
' Reports: Final(1) & Limits(3)
' Skip Sensitivity Report(2)...(Not an integer Problem !!)
SolverFinish 1, ReportArray:=Array(1, 3)

--
HTH :>)
Dana DeLouis
Windows XP & Office 2003


Kragelund said:
Dana,

your input was spot on! My eventual objective was to use an external input
to vary the size of the ranges. As the ranges are not of similar sizes, I
did
indeed need the Union of the ranges, I hadn't thought of this possibility.
I
am posting my functional code below if somebody would find it useful. The
source of inspiration should be quite evident.

Can you btw. recommend a good reference book specifically on Solver, or
one
which deals in detail with Solver? The VBA reference books I've seen so
far
like Walkenbach's barely mention Solver, and I'd like to do more with
Solver,
like packing it into a dll.file for instance and perhaps manipulate the
reports that Solver generates, or simply control these reports via VBA
code.

Thanks a lot for your help.

Henrik Kragelund

Sub Solver2()

Dim Target As String
Dim ChgCells As String

Dim Rng3 As Variant

Dim CellA_Start As Range
Dim CellB_Start As Range

Dim i As Integer
Dim j As Integer

i = Cells(17, 2).Value
j = Cells(17, 3).Value

Set CellA_Start = Range("H45")
rindexA = CellA_Start.Row
colindexA = CellA_Start.Column
Set CellA_End = Cells(rindexA + 1, colindexA + i)
Set RngA = Range(CellA_Start, CellA_End)

Set CellB_Start = Range("H47")
rindexB = CellB_Start.Row
colindexB = CellB_Start.Column
Set CellB_End = Cells(rindexB + 1, colindexB + j)
Set RngB = Range(CellB_Start, CellB_End)

Set Rng3 = [H40:BB40]

'Range(ChgCells).ClearContents
Application.DisplayAlerts = False

Target = Cells(27, 7).Address
ChgCells = Union(RngA, RngB).Address

'Now Change to Strings...
RngA = RngA.Address
RngB = RngB.Address
Rng3 = Rng3.Address

SolverReset
Solver.SolverOptions MaxTime:=300, Iterations:=5000, Precision:=0.0001,
Convergence:=0.0001
SolverOk Target, 1, , ChgCells

SolverAdd RngA, 1, 0
SolverAdd RngA, 3, -42000
SolverAdd RngB, 1, 0
SolverAdd RngB, 3, -42000
SolverAdd Rng3, 3, 0

SolverSolve

End Sub


Dana DeLouis said:
"...The Solver has to iterate over two separate ranges.
Click to expand...

Hi. Just guessing. Your "Changing Cells" is one big area...
ByChange:=Range(cell2, cell4).Address
Click to expand...

Debug.Print Range(cell2, cell4).Address
returns:
$H$45:$AJ$48

Is this what you expect?
You may have meant the Union of these two ranges:
Debug.Print Union(cell2, cell4).Address
$H$45:$AG$46,$H$47:$AJ$48
UserFinish = True
SolverSolve
Click to expand...

I believe it should be written as follows:

SolverSolve UserFinish:=True
or just ...
SolverSolve True
SOLVER.SolverReset
SOLVER.SolverOk SetCell:=cell1.Address, MaxMinVal:=1, ValueOf:="0",
Click to expand...

If you wish, you can just use the following...

SolverReset
SolverOk SetCell:=???, MaxMinVal:=1, ByChange:=???

Note that you do not need to include ValueOf = 0 in a Maximization
problem,
as it's ignored anyway.

This is not quite how I would do it, but it may give you some ideas of
your
own...

Sub DemoSolver()

Dim Target As String
Dim ChgCells As String
Dim Rng1 As Variant
Dim Rng2 As Variant

Set Rng1 = [H45:AG46]
Set Rng2 = [H47:AJ48]

Target = [G27].Address
ChgCells = Union(Rng1, Rng2).Address

'Now Change to Strings...
Rng1 = Rng1.Address
Rng2 = Rng2.Address
' = = = = = = = = = =
SolverReset
SolverOk Target, 1, , ChgCells

SolverAdd Rng1, 1, 0
SolverAdd Rng1, 3, -42000
SolverAdd Rng2, 1, 0
SolverAdd Rng2, 3, -42000

SolverSolve True
End Sub

Hope this helps in some way...
--
Dana DeLouis
Windows XP & Office 2003


Kragelund said:
Hi all,

I am trying to set up a VBA version of a Solver model, which works fine
in
"worksheet mode". The Solver has to iterate over two separate ranges.
When
using the recorded macro the input values are: ByChange:=
"$H$45:$AG$46,$H$47:$AJ$48". They work fine. When I try to replace the
values
with variables, I get an error message, saying something like "global
failed". Can anybody help me with this annoying problem?

Also I'd very much like to know how Solver evaluates whether a given
problem
is "too large". I get that message sometimes on the exact same number
of
cells which worked just minutes before (in worksheet mode, without
VBA).

The dysfunctional code is this:

Sub SolverMakro()

Dim cell1 As Range, cell2 As Range, Cell3 As Range, cell4 As Range,
cell5
As
Range

Set cell1 = Range("G27")
Set cell2 = Range("H45:AG46")
'Set Cell3 = Range("AG46")
Set cell4 = Range("H47:AJ48")
'Set cell5 = Range("AJ48")

SOLVER.SolverReset
SOLVER.SolverOk SetCell:=cell1.Address, MaxMinVal:=1, ValueOf:="0",
ByChange:=Range(cell2, cell4).Address

SolverAdd CellRef:=Range(cell2).Address, Relation:=1,
FormulaText:="0"
SolverAdd CellRef:=Range(cell2).Address, Relation:=3,
FormulaText:="-42000"
SolverAdd CellRef:=Range(cell4).Address, Relation:=1,
FormulaText:="0"
SolverAdd CellRef:=Range(cell4).Address, Relation:=3,
FormulaText:="-42000"

SolverAdd CellRef:="$H$39:$BB$39", Relation:=1, FormulaText:="0"

UserFinish = True
SolverSolve

End Sub

Thanks in advance,

Kragelund
Click to expand...
Click to expand...
Click to expand...
 
K

Kragelund

Dana,

Sorry, I almost forgot to respond (Christmas stress). I'll give your
proposal a go, thanks so much for your input.

Rgds,

Henrik Kragelund

Dana DeLouis said:
...or simply control these reports via VBA code.
Click to expand...

Hi. Here are just some thoughts...
This is not necessary, but maybe you could calculate the number of columns
as you are, and then do...

NumColumns = 7
Set RngA = CellA_Start.Resize(1, NumColumns)

Here are some ideas...

' Solve w/ no prompts
SolverSolve True
' Keep Final values
' Reports: Final(1) & Limits(3)
' Skip Sensitivity Report(2)...(Not an integer Problem !!)
SolverFinish 1, ReportArray:=Array(1, 3)

--
HTH :>)
Dana DeLouis
Windows XP & Office 2003


Kragelund said:
Dana,

your input was spot on! My eventual objective was to use an external input
to vary the size of the ranges. As the ranges are not of similar sizes, I
did
indeed need the Union of the ranges, I hadn't thought of this possibility.
I
am posting my functional code below if somebody would find it useful. The
source of inspiration should be quite evident.

Can you btw. recommend a good reference book specifically on Solver, or
one
which deals in detail with Solver? The VBA reference books I've seen so
far
like Walkenbach's barely mention Solver, and I'd like to do more with
Solver,
like packing it into a dll.file for instance and perhaps manipulate the
reports that Solver generates, or simply control these reports via VBA
code.

Thanks a lot for your help.

Henrik Kragelund

Sub Solver2()

Dim Target As String
Dim ChgCells As String

Dim Rng3 As Variant

Dim CellA_Start As Range
Dim CellB_Start As Range

Dim i As Integer
Dim j As Integer

i = Cells(17, 2).Value
j = Cells(17, 3).Value

Set CellA_Start = Range("H45")
rindexA = CellA_Start.Row
colindexA = CellA_Start.Column
Set CellA_End = Cells(rindexA + 1, colindexA + i)
Set RngA = Range(CellA_Start, CellA_End)

Set CellB_Start = Range("H47")
rindexB = CellB_Start.Row
colindexB = CellB_Start.Column
Set CellB_End = Cells(rindexB + 1, colindexB + j)
Set RngB = Range(CellB_Start, CellB_End)

Set Rng3 = [H40:BB40]

'Range(ChgCells).ClearContents
Application.DisplayAlerts = False

Target = Cells(27, 7).Address
ChgCells = Union(RngA, RngB).Address

'Now Change to Strings...
RngA = RngA.Address
RngB = RngB.Address
Rng3 = Rng3.Address

SolverReset
Solver.SolverOptions MaxTime:=300, Iterations:=5000, Precision:=0.0001,
Convergence:=0.0001
SolverOk Target, 1, , ChgCells

SolverAdd RngA, 1, 0
SolverAdd RngA, 3, -42000
SolverAdd RngB, 1, 0
SolverAdd RngB, 3, -42000
SolverAdd Rng3, 3, 0

SolverSolve

End Sub


Dana DeLouis said:
"...The Solver has to iterate over two separate ranges.

Hi. Just guessing. Your "Changing Cells" is one big area...

ByChange:=Range(cell2, cell4).Address

Debug.Print Range(cell2, cell4).Address
returns:
$H$45:$AJ$48

Is this what you expect?
You may have meant the Union of these two ranges:
Debug.Print Union(cell2, cell4).Address
$H$45:$AG$46,$H$47:$AJ$48

UserFinish = True
SolverSolve

I believe it should be written as follows:

SolverSolve UserFinish:=True
or just ...
SolverSolve True

SOLVER.SolverReset
SOLVER.SolverOk SetCell:=cell1.Address, MaxMinVal:=1, ValueOf:="0",

If you wish, you can just use the following...

SolverReset
SolverOk SetCell:=???, MaxMinVal:=1, ByChange:=???

Note that you do not need to include ValueOf = 0 in a Maximization
problem,
as it's ignored anyway.

This is not quite how I would do it, but it may give you some ideas of
your
own...

Sub DemoSolver()

Dim Target As String
Dim ChgCells As String
Dim Rng1 As Variant
Dim Rng2 As Variant

Set Rng1 = [H45:AG46]
Set Rng2 = [H47:AJ48]

Target = [G27].Address
ChgCells = Union(Rng1, Rng2).Address

'Now Change to Strings...
Rng1 = Rng1.Address
Rng2 = Rng2.Address
' = = = = = = = = = =
SolverReset
SolverOk Target, 1, , ChgCells

SolverAdd Rng1, 1, 0
SolverAdd Rng1, 3, -42000
SolverAdd Rng2, 1, 0
SolverAdd Rng2, 3, -42000

SolverSolve True
End Sub

Hope this helps in some way...
--
Dana DeLouis
Windows XP & Office 2003


Hi all,

I am trying to set up a VBA version of a Solver model, which works fine
in
"worksheet mode". The Solver has to iterate over two separate ranges.
When
using the recorded macro the input values are: ByChange:=
"$H$45:$AG$46,$H$47:$AJ$48". They work fine. When I try to replace the
values
with variables, I get an error message, saying something like "global
failed". Can anybody help me with this annoying problem?

Also I'd very much like to know how Solver evaluates whether a given
problem
is "too large". I get that message sometimes on the exact same number
of
cells which worked just minutes before (in worksheet mode, without
VBA).

The dysfunctional code is this:

Sub SolverMakro()

Dim cell1 As Range, cell2 As Range, Cell3 As Range, cell4 As Range,
cell5
As
Range

Set cell1 = Range("G27")
Set cell2 = Range("H45:AG46")
'Set Cell3 = Range("AG46")
Set cell4 = Range("H47:AJ48")
'Set cell5 = Range("AJ48")

SOLVER.SolverReset
SOLVER.SolverOk SetCell:=cell1.Address, MaxMinVal:=1, ValueOf:="0",
ByChange:=Range(cell2, cell4).Address

SolverAdd CellRef:=Range(cell2).Address, Relation:=1,
FormulaText:="0"
SolverAdd CellRef:=Range(cell2).Address, Relation:=3,
FormulaText:="-42000"
SolverAdd CellRef:=Range(cell4).Address, Relation:=1,
FormulaText:="0"
SolverAdd CellRef:=Range(cell4).Address, Relation:=3,
FormulaText:="-42000"

SolverAdd CellRef:="$H$39:$BB$39", Relation:=1, FormulaText:="0"

UserFinish = True
SolverSolve

End Sub

Thanks in advance,

Kragelund
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

how to use Solver in Excel VBA 1
Solver iteration in VBA macro 2
Set solver constraint not working in VB 6
Macro Skip the Constraint (Solver) 0
VBA code to RUN Solver mutiple times (on different target cells) 8
Solver ignores constraints in protected sheets XL 2003 1
Macro for user defined Solver changing cells. 10
Excel Solver in VB 4

Top