How to repeat functions in Excel Solver (using Macros)
How to repeat functions in Excel Solver (using Macros)
(OP)
Hi,
I'm finally getting this macro to work with the Excel Solver Function:
But right now that's what I'm doing... copying and pasting for every row. How can I modify this code to cover a range of cells? Do I have to name my ranges? What's the syntax for that? When I try to input a range of cells for the solver function, it tells me that the target cell can only be a single cell.
I appreciate your help as always.
Thanks!
I'm finally getting this macro to work with the Excel Solver Function:
CODE
Sub EthBal3()
'
' EthBal3 Macro
' Macro recorded 12/30/2005 by cabelkm
'
'
SolverOk SetCell:="$AF$5", MaxMinVal:=3, ValueOf:="0", ByChange:="$AA$5"
SolverSolve Userfinish:=True
SolverOk SetCell:="$AF$6", MaxMinVal:=3, ValueOf:="0", ByChange:="$AA$6"
SolverSolve Userfinish:=True
SolverOk SetCell:="$AF$7", MaxMinVal:=3, ValueOf:="0", ByChange:="$AA$7"
SolverSolve Userfinish:=True
SolverOk SetCell:="$AF$8", MaxMinVal:=3, ValueOf:="0", ByChange:="$AA$8"
SolverSolve Userfinish:=True
End Sub
'
' EthBal3 Macro
' Macro recorded 12/30/2005 by cabelkm
'
'
SolverOk SetCell:="$AF$5", MaxMinVal:=3, ValueOf:="0", ByChange:="$AA$5"
SolverSolve Userfinish:=True
SolverOk SetCell:="$AF$6", MaxMinVal:=3, ValueOf:="0", ByChange:="$AA$6"
SolverSolve Userfinish:=True
SolverOk SetCell:="$AF$7", MaxMinVal:=3, ValueOf:="0", ByChange:="$AA$7"
SolverSolve Userfinish:=True
SolverOk SetCell:="$AF$8", MaxMinVal:=3, ValueOf:="0", ByChange:="$AA$8"
SolverSolve Userfinish:=True
End Sub
But right now that's what I'm doing... copying and pasting for every row. How can I modify this code to cover a range of cells? Do I have to name my ranges? What's the syntax for that? When I try to input a range of cells for the solver function, it tells me that the target cell can only be a single cell.
I appreciate your help as always.
Thanks!





RE: How to repeat functions in Excel Solver (using Macros)
For example:
CODE
Dim C As Range
For Each C in [$AF$5:$AF$8]
SolverOk SetCell:=C.Address, MaxMinVal:=3, ValueOf:="0", ByChange:=C.Offset(0,-5).Address
SolverSolve Userfinish:=True
Next C
End Sub
Your other option is to loop with a counter:
CODE
Dim i As Long
For i=5 To 8
SolverOk SetCell:=Cells(i,32).Address, MaxMinVal:=3, ValueOf:="0", ByChange:=Cells(i,27).Address
SolverSolve Userfinish:=True
Next i
End Sub
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: How to repeat functions in Excel Solver (using Macros)
looks good, but will the solver code need to be reset (i.e .solver reset) after each solution found? just curious . . .
also, sometimes the macro errors on the "Solver Ok", as the macro thinks the sub or function name is not defined.
like yourself, have not tested it . . .
OilBoiler,
there are a couple of techniques to naming ranges.
from the main menu: Insert-Name-Define
or
use the name box, which is located to left of formula bar with a little pull-down option.
no, you do not have to name the ranges as demonstrated by the code written by joerd. but, for some users, naming helps in simplifying and referencing certain cell(s) within a workbook.
as far as the error message received, yes, when solver tries to find a solution, only one cell can be established as the target cell.
good luck!
-pmover
RE: How to repeat functions in Excel Solver (using Macros)
Before you use this function, you must establish a reference to the Solver add-in. With a Visual Basic module active, click References on the Tools menu, and then select the Solver.xla check box under Available References. If Solver.xla doesn't appear under Available References, click Browse and open Solver.xla in the \Office\Library subfolder.
It looks like you don't need all the .Address stuff, but can just reference the range instead of providing the strings.
SolverReset sounds like a good one, especially at the start of the procedure, just in case a user has set some options.
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.