×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

How to repeat functions in Excel Solver (using Macros)

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:

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

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)

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:

CODE

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:

CODE

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 "$AF$" & i instead of the Cells(i,32).Address construction. Whatever works best for you.

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)

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

RE: How to repeat functions in Excel Solver (using Macros)

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources