Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 + Solver | Excel Worksheet Functions | |||
import Excel 2003 file into Outlook 2003 - NO NAMED RANGES?? | Excel Discussion (Misc queries) | |||
how do you use solver in excel 2003 | Excel Discussion (Misc queries) | |||
Solver and Excel 2003 | Excel Programming | |||
Excel 2003 + Solver + C# | Excel Programming |