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