How to repeat functions in Excel Solver (using Macros) 1

[OilBoiler]

Hi,

I'm finally getting this macro to work with the Excel Solver Function:

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

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!

 

[joerd]

You can loop through a range by using a For Each .. Next loop, but you'll have to use Offset to refer to the changing cell.
For example:

Sub EthBal3()
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

I don't know for sure if I haven't made a typo, since I didn't try it in Excel myself.
Your other option is to loop with a counter:

Sub EthBal3()
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

Yet another option is to use a string concatenation like [tt]"$AF$" & i [/tt] instead of the [tt]Cells(i,32).Address[/tt] construction. Whatever works best for you.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[pmover]

joerd,

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

 

[joerd]

You have to set a reference to the solver.xla (see Excel VB help):
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.