Having problems w/ Solver code

[Guest]

I am writing solver code behind an excel spreasheet and am having difficulty
making it work. The code is listed below:


Sub signif_control_pop()

Dim cntlpc
Dim pop

'Clear any previous solver settings.
SOLVERReset

'Prompt user for population size.
pop = Application.InputBox(Prompt:="Population Total:", Type:=1)
Range("C3") = pop

'Set targeted cell, H16, to a value by changing the range of acceptable
percentages.
SolverOK SetCell:=Range("H16"), MaxMinVal:=3, _
ValueOf:=0.95, _
byChange:=Range("B5")

' Solve the model and keep the final results.
SolverSolve Userfinish:=False

'Save results of control percent before you discard the results.
cntl_pc = Range("B5")

SolverFinish KeepFinal:=1 'NOTE: Does not work if I include ReportArray:=1

' Show the result in a message box.
MsgBox "The Control Population Percent for " & pop & " is " & cntl_pc & " "

End Sub


The problem I'm having is regarding the "ByChange" cell (B5) used in the
solver function. The value in the cell never changes. When the message box is
displayed, it always displays the same number, which is the number that was
sitting in cell B5 before I ran the program. I have included "SolverFinish
KeepFinal:= 1" in the code.

If I do not run the visual basic code, but rather manually run Solver in
excel, it will change cell as I want it to do. Any help is appreciated.

Stacy

 

[Jon Peltier]

As I answered in another forum, Solver takes a string address, not a range.
This should work:

SolverOK SetCell:="$H$16", MaxMinVal:=3, _
ValueOf:=0.95, _
byChange:="$B$5"

- Jon

 

[Dana DeLouis]

SolverFinish KeepFinal:=1 'NOTE: Does not work if I include
Hi. I don't have the op's original post.

Try...

ReportArray:=Array(1)

Your code "should" work. For debugging your problem, try the following and
see what is returned to "Result."

Result = SOLVERSOLVE(True)
MsgBox Result

SOLVERFINISH 1, Array(1)
cntl_pc = Range("B5")
'...ect

 

[Guest]

Thanks for your help. I've tried this but it still doesn't work. Solver
doesn't appear to work at all, but since my code does not return any errors,
I cannot figure this out. Everything seems fine in my References. Any further
help is appreciated.