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