I am attempting to use the Solver tool multiple times on an excel
spread sheet and would like to create either a macro or some other
iteration tool so as to avoid having hand edit changes in the solver
for each iteration.

To be more clear, I currently have over 300 observations. Each
observation is its own row. For each observation, there are two
"unknown" parameters (currently in columns AN and AO) and two
constrain equations in columns AL and AM. The Solver tool finds values
for AN and AO such that AL and AM both equal 0. While I can
successfully manage to use solver for one row at a time, for each
observation I currently have to open solver, change the reference
points down one row, and then solve again. I tried to write a macro
which would do this for me (please see below) but keep getting an
error message.

' Keyboard Shortcut: Ctrl+q
'
    SolverOk SetCell:="$AM$19", MaxMinVal:=3, ValueOf:="0", ByChange:=
_
        "$AN$19:$AO$19"
    SolverDelete CellRef:="$AL$18", Relation:=2, FormulaText:="0"
    SolverAdd CellRef:="$AL$19", Relation:=2, FormulaText:="0"""
    SolverOk SetCell:="$AM$19", MaxMinVal:=3, ValueOf:="0", ByChange:=
_
        "$AN$19:$AO$19"
    SolverSolve
End Sub

Could someone please tell me how to iterate this process efficiently.
(If it is helpful I could easily send you the spreadsheet in
question.)

Cordially,

Alexandra

---

Clarification of Question by alexandrag-ga on 22 Oct 2002 12:58 PDT

Hi. I was asked to post an example spreadsheet. I have done so on my
webpage at: http://pantheon.yale.edu/~agg7 under the title "test
spreadsheet". (its at the bottom of the page.)

Dear RAC-GA. Thanks so much for your Macro, I will give it a shot and
let you know how it goes.

---

Clarification of Question by alexandrag-ga on 22 Oct 2002 13:17 PDT

Dear RAC

Thanks again for your help. When I run the macro, I get the message
"Sub or Function Not Defined" for the line SolverReset. I am going to
go try this on another computer in case this is a problem to my
specific version of excel or computer. I'm running a fairly recent
version (9.0) so don't think this is the problem. Any suggestions. By
the way, thank you again. Alexandra

---

Clarification of Question by alexandrag-ga on 23 Oct 2002 09:01 PDT

Dear RAC,

Thank you so, so much. In the end, I actually had to go and re-install
Excel (for some reason while I could use Solver, I couldn't get the
solver option box in Visual Basics to even appear so I could check
it), but once I did that your macro worked like a charm on that
spreadsheet and others like it I had.

This is for my dissertation so again I say thank you.

Alexandra

Hi,
Can you please load the sample spreadsheet in a common webserver and
give the link. This will help to write a macro for you.

Thanks,
RAC

Hi Alexandra,
     Please try this simple Macro. 

Sub MacroSolve()
Worksheets("Sheet1").Activate
Rowcount = 2
Do While Not IsEmpty(Worksheets("Sheet1").Range("A" & Rowcount))
    SolverReset
    SolverOptions precision:=0.001
    SolverOk SetCell:=Range("AM" & Rowcount), _
        MaxMinVal:=3, _
        ValueOf:="0", _
        ByChange:=Range("AN" & Rowcount & ":AO" & Rowcount)
    SolverAdd CellRef:=Range("AL" & Rowcount), _
        Relation:=2, _
        FormulaText:=0
    SolverSolve userFinish:=True
    SolverFinish keepFinal:=1
    Rowcount = Rowcount + 1
Loop
MsgBox "processing over"

End Sub

As there is no sample xls file from you,I assumed some of values.
1.The program assumes that your Solver equ is in sheet "Sheet1"
If the sheet name is different, change in the macro accordingly.

2.It assumes that your solver equ starts from Row 2("AM2"). If your equ starts
from other row (ex:) 19, then change the 
Rowcount = 2 to Rowcount = 19

3. The macro will repeat the calculation until it finds a empty cell in "A" Column.

How it works:
First it activates the "Sheet1"
Set the Rowcount to 2.
Verifies A column and Rowcount cell (During firstrun it is"A2")
If it is not empty then it Resets the solver.
This will remove any equation set in solver.
Set the Solver target cell(AM2), By changing cell(AN2 and AO2),
Adds one contraint (AL2 = 0)
Solves and Keep the final values.
Increment the Row count by 1
Repeat the process untill it finds the cell in A column is empty.
Display Message box "Processing Over"

How to Run:
Copy this macro 
Open your Excel file which contains data. 
In the tools menu --Macro--Visual Basic Editor. 
Paste the macro in the VB editor screen.
Do whatever modifcation is needed. 
Save it. 
To run it from menuTools-->Macro-->Macros-->Select
the macro name "MacroSolve" Select Run. 
 
If you have any Clarification on the procedure please let me know.  
Wish you best of luck.

RAC

Hi Alexandra,

Your error shows "Sub or Function Not Defined" the solver function is
not loaded in your PC. Solver is a excel add on product. To correct
the error
do as below.

Open your excel file which contains macro
Go to Visual Basic editor in Excel  Tools -->Macro-->Visual Basic
editor
From the Tools menu In Visual basic editor Select References.
From the popup tick the checkbox for Solver.xls.
Say OK.
Now run your macro.

I will download and check your xls sheet and let you know.

Thanks,
RAC

Hi Alexandra,      
I checked your excel sheet. The program is working fine.
 Only modification needed is in the macro change the "sheet1" to your
sheet name
"tri_results".

Also before running the macro makesure that the cells AL and AM has
valid results. No Div/0 error.

Please let me know if you have any doubts.

Thanks,
RAC