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