Using a simple solver idea using VBA - ByChange cells don't change?

[MartinL]

I am having trouble getting my sheet to shop the results after running my Macro.

I am using 1 of 3 CommandButtons to run 1 of 3 Macros - Scenarios (and there has to be a better way of this)

example

C9:C12 have the values to adjust
C13 is the Objective Value
H15 is the value to Match

(Button 2 = C14, H16)
(Button 3 = C15, H17)

My 3 buttons are very similar but here is Macro 1

Private Sub CommandButton1_Click()
'
' Macro1 Macro
' Calculate widgets required to match Subscription Gross Margin
'
'
SolverReset


    ' Set up your solver here...
    SolverOk SetCell:="$C$13", MaxMinVal:=3, ValueOf:="$G$15", ByChange:="$C$9:$C$12", _
        Engine:=1, EngineDesc:="GRG Nonlinear"


    ' Execute solve
    SolverSolve True


End Sub

The problem is if I run the solver manually it works
If I use the VBA V9:C12 do not change

[Note: SolverSolve True - This eliminates the dialog box]


any ideas

 

[Worf]

The code below works for me from a command button. Try running Solver manually once and then click the button.
Another thing to try is to eliminate the button and test the code from a standard module.

' this goes at the sheet module
Private Sub CommandButton1_Click()
Dim objc$, vof
objc = "$d$6"                       ' address of objective cell
vof = [b16]                         ' cell containing desired value
SolverOk SetCell:=objc, MaxMinVal:=3, ValueOf:=vof, ByChange:="$B$3:$B$5", _
Engine:=2, EngineDesc:="Simplex LP"
SolverSolve
End Sub

 

[MartinL]

The code below works for me from a command button. Try running Solver manually once and then click the button.
Another thing to try is to eliminate the button and test the code from a standard module.

Thanks Worf
Live Long and Prosper

 

[Worf]

You are welcome.

Place a message box in the code to make sure it is running.