Re: Using Solver with multiple ranges (Excel 2003 sp2)

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Tom,
thanks for your input. I needed the Union as you pointed out. You are too
rigth about the self-imposed errors. My trouble with Excel is that sometimes
you don't actually get to know how you failed, because the program crashes or
the error messages do not really say a lot unless you already know what they
mean. I suppose there is only way forward, back on the horse again ...

Henrik Kragelund

"Tom Ogilvy" wrote:

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" <Kragelund@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:483AD2CB-DE2E-4EFF-9433-A8268DFFAB3E@xxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • Re: How to record macro for Solver in Excel?
    ... The vba code portion does very little processing of the input. ... "SolvOk" are listed on the Solver4 Class module. ... > Tom Ogilvy ... The solution was to use Solv instead of Solver. ...
    (microsoft.public.excel.programming)
  • Re: Excel 2002 Solver unexpected Error
    ... Hallo Michael, ... Das Macro selber ist nicht passwortgeschützt, ... Extras den Solver aufrufe und gleich wieder schliesse, ... Gruss Tom ...
    (microsoft.public.de.excel)
  • Re: ...That label/1 predicates real efficiency?
    ... constraint solver. ... Then Tom Schrijvers provided a CHR implementation - generic in the sense that it allows anyone to write his/her own constraint solvers with their favourite propagation/completeness/domain/etc. ... Tom also wrote a finite domain solver (named bounds in SWI) but mostly as a proof that it can be done in SWI - not pretending to be something super duper efficient. ...
    (comp.lang.prolog)