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.