Another Solver Problem
 

Sorry for this post, I'm pretty new to both Excel and
Visual Basic. I'm trying to automate a solver call in
Visual Basic, but I keep getting error messages. One says
"Set Target Cell must be a single cell on the active sheet"
with only an "OK" button on it. However, when I hit OK, the
solver doesn't compute anything, just gives me a zero. This
error appears everytime I run the script. I tried recording
my actions with a macro, then copied and pasted the code
from that macro into that of another, larger VB macro. Here
is the cod I'm using:

' totalcols is the number of total columns
temp = = Sheets("sheet5").Range(Cells(2, 5),_
Cells(totalcols, 5)).Address

Cells(totalcols + 2, 5).Select

SolverOk SetCell:="cells(totalcols+2,5)", MaxMinVal:=1,_
ValueOf:="0", ByChange:=" & temp & "

SolverAdd CellRef:="cells(totalcols+1,5)", Relation:=1,_
FormulaText:="cells(totalcols+1,4)"

SolverOk SetCell:="cells(totalcols+2,5)", MaxMinVal:=1,_
ValueOf:="0", ByChange:=" & temp & "

SolverAdd CellRef:="cells(totalcols+3,5)", Relation:=1,_
FormulaText:="cells(totalcols+3,4)"

SolverOk SetCell:="cells(totalcols+2,5)", MaxMinVal:=1,_
ValueOf:="0", ByChange:=" & temp & "

SolverSolve

When I execute this code from a macro by itself and without
the variables (the original recorded macro without the
variables) I get a different error: every time you run
solver, an option pops up asking if you want to "Keep
Solver Solution" or "Restore Original Values". Above this,
there is a message: "Error in model. Please verify that the
cells and constraints are valid". Here is that code:


Range("E10").Select

SolverOk SetCell:="$E$10", MaxMinVal:=1, ValueOf:="0",_
ByChange:="$E$2:$E$8"

SolverOk SetCell:="$E$10", MaxMinVal:=1, ValueOf:="0",_
ByChange:="$E$2:$E$8"

SolverOk SetCell:="$E$10", MaxMinVal:=1, ValueOf:="0",_
ByChange:="$E$2:$E$8"

SolverAdd CellRef:="$E$9", Relation:=1, FormulaText:="$D$9"

SolverAdd CellRef:="$E$11", Relation:=1,_ FormulaText:="$D$11"

SolverOk SetCell:="$E$10", MaxMinVal:=1, ValueOf:="0",_
ByChange:="$E$2:$E$8"

SolverOptions MaxTime:=100, Iterations:=100,_
Precision:=0.000001, AssumeLinear _
:=False, StepThru:=False, Estimates:=1, Derivatives:=1,_
SearchOption:=1, _
IntTolerance:=5, Scaling:=False, Convergence:=0.0001_,
AssumeNonNeg:=True

SolverOk SetCell:="$E$10", MaxMinVal:=1, ValueOf:="0",_
ByChange:="$E$2:$E$8"

SolverSolve


If I just recorded my use of the solver into a macro, why
then is it having problems duplicating the results? I can
go back and manually run solver again without errors. Am I
doing something wrong with the way I'm coding this? Thanks
in advance.

Another Solver Problem
 

Hello. I can't really follow, so here is just a general idea.

This part of your code is listed multiple times, so I think you are doing
something wrong with your translation from the macro recorder to your own
version.

SolverOk SetCell:="$E$10", MaxMinVal:=1, ValueOf:="0",_
ByChange:="$E$2:$E$8"

SolverOk SetCell:="$E$10", MaxMinVal:=1, ValueOf:="0",_
ByChange:="$E$2:$E$8"

SolverOk SetCell:="$E$10", MaxMinVal:=1, ValueOf:="0",_
ByChange:="$E$2:$E$8"

Also, in this part of your code...

SolverOk SetCell:="cells(totalcols+2,5)", MaxMinVal:=1,_
ValueOf:="0", ByChange:=" & temp & "

This is listed multiple times also. There really should be only 1
"SolverOK" per problem.

Also, your code for the particular cell includes "cells(totalcols+2,5)".
This is a string that does not hold anything meaningful to Solver. It is
expecting an Address. This is a string that does not look like an Address.
If you wish to use Cells(...), then here is an idea.

SolverOk SetCell:=Cells(totalcols+2,5).Address, MaxMinVal:=1, ByChange:=
Range("Temp").Address

(No need for ValueOf)

Anyway, this is just quick and dirty. Nothing tested, but maybe something
to get you going. HTH.

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"new user" wrote in message
...
Sorry for this post, I'm pretty new to both Excel and
Visual Basic. I'm trying to automate a solver call in
Visual Basic, but I keep getting error messages. One says
"Set Target Cell must be a single cell on the active sheet"
with only an "OK" button on it. However, when I hit OK, the
solver doesn't compute anything, just gives me a zero. This
error appears everytime I run the script. I tried recording
my actions with a macro, then copied and pasted the code
from that macro into that of another, larger VB macro. Here
is the cod I'm using:

' totalcols is the number of total columns
temp = = Sheets("sheet5").Range(Cells(2, 5),_
Cells(totalcols, 5)).Address

Cells(totalcols + 2, 5).Select

SolverOk SetCell:="cells(totalcols+2,5)", MaxMinVal:=1,_
ValueOf:="0", ByChange:=" & temp & "

SolverAdd CellRef:="cells(totalcols+1,5)", Relation:=1,_
FormulaText:="cells(totalcols+1,4)"

SolverOk SetCell:="cells(totalcols+2,5)", MaxMinVal:=1,_
ValueOf:="0", ByChange:=" & temp & "

SolverAdd CellRef:="cells(totalcols+3,5)", Relation:=1,_
FormulaText:="cells(totalcols+3,4)"

SolverOk SetCell:="cells(totalcols+2,5)", MaxMinVal:=1,_
ValueOf:="0", ByChange:=" & temp & "

SolverSolve

When I execute this code from a macro by itself and without
the variables (the original recorded macro without the
variables) I get a different error: every time you run
solver, an option pops up asking if you want to "Keep
Solver Solution" or "Restore Original Values". Above this,
there is a message: "Error in model. Please verify that the
cells and constraints are valid". Here is that code:


Range("E10").Select

SolverOk SetCell:="$E$10", MaxMinVal:=1, ValueOf:="0",_
ByChange:="$E$2:$E$8"

SolverOk SetCell:="$E$10", MaxMinVal:=1, ValueOf:="0",_
ByChange:="$E$2:$E$8"

SolverOk SetCell:="$E$10", MaxMinVal:=1, ValueOf:="0",_
ByChange:="$E$2:$E$8"

SolverAdd CellRef:="$E$9", Relation:=1, FormulaText:="$D$9"

SolverAdd CellRef:="$E$11", Relation:=1,_ FormulaText:="$D$11"

SolverOk SetCell:="$E$10", MaxMinVal:=1, ValueOf:="0",_
ByChange:="$E$2:$E$8"

SolverOptions MaxTime:=100, Iterations:=100,_
Precision:=0.000001, AssumeLinear _
:=False, StepThru:=False, Estimates:=1, Derivatives:=1,_
SearchOption:=1, _
IntTolerance:=5, Scaling:=False, Convergence:=0.0001_,
AssumeNonNeg:=True

SolverOk SetCell:="$E$10", MaxMinVal:=1, ValueOf:="0",_
ByChange:="$E$2:$E$8"

SolverSolve


If I just recorded my use of the solver into a macro, why
then is it having problems duplicating the results? I can
go back and manually run solver again without errors. Am I
doing something wrong with the way I'm coding this? Thanks
in advance.