Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Using Solver with multiple ranges (Excel 2003 sp2)
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 34
Default Using Solver with multiple ranges (Excel 2003 sp2)
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 a 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Using Solver with multiple ranges (Excel 2003 sp2)
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.

--
Regards,
Tom Ogilvy


"Kragelund" wrote in message
...
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 a 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 947
Default Using Solver with multiple ranges (Excel 2003 sp2)
"...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


"Kragelund" wrote in message
...
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 a 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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 947
Default Using Solver with multiple ranges (Excel 2003 sp2)
...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" wrote in message
...
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" wrote:

"...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


"Kragelund" wrote in message
...
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 a 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





Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 34
Default Using Solver with multiple ranges (Excel 2003 sp2)
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" wrote:

...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" wrote in message
...
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" wrote:

"...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


"Kragelund" wrote in message
...
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 a 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






Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2003 + Solver xxsawer Excel Worksheet Functions 3 December 8th 09 10:20 AM
import Excel 2003 file into Outlook 2003 - NO NAMED RANGES?? lewisma9 Excel Discussion (Misc queries) 0 February 27th 07 12:23 AM
how do you use solver in excel 2003 Larry Orange Excel Discussion (Misc queries) 3 November 3rd 06 01:35 PM
Solver and Excel 2003 Kevin Beckham Excel Programming 1 June 2nd 04 05:24 AM
Excel 2003 + Solver + C# David Excel Programming 0 December 4th 03 03:50 PM


All times are GMT +1. The time now is 02:16 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement