Problems with solver and vba
 

Dear all,
i'm trying to create a Visual Basic Macro that uses Excel
Solver.
I simply put on cell A1 a numeric value (example 3) and
on cell B1 the formula =A1*A1
Then in VBA i write the following:

-----------------------------------------------------
Sub Find_Square_Root()

' Set up the parameters for the model.
' Set the target cell A2 to a Value of 50 by changing
cell A1.
SolverOK SetCell:=Range("A2"), MaxMinVal:=3,
ValueOf:=50, _
ByChange:=Range("A1")

' Solve the model but do not display the Solver
Results dialog box.
SolverSolve UserFinish:=True

' Finish and keep the final results.
SolverFinish KeepFinal:=1

End Sub
----------------------------------------------------------

Unfortunately by executing the macro from EXCEL nothing
happens. It seems that SOLVER is not able to load the
variables.
I can solve the problem by making a pre-run of SOLVER
from EXCEL setting the target cells and the cells to be
modified by hand.
Once that this pre-run is done I can load the macro that
works perfectly.

Is there any way to avoid initialising SOLVER from EXCEL
by hand and let it work from the macro?

thanks
Marco

Problems with solver and vba
 

look at
http://support.microsoft.com/default...ver/solver.asp

"

Problems with solver and vba
 

-----Original Message-----
look at
http://support.microsoft.com/default.aspx?
scid=/support/excel/content/solver/solver.asp

"


.


To be honest i just tried to make the axample work before
posting my first message. Unfortunately it didn't work
without making the solver pre-run from excel.
Try to copy and paste these lines from
http://support.microsoft.com/default.aspx?
scid=/support/excel/content/solver/solver.asp


Sub Find_Square_Root2()

Dim val
Dim sqroot

' Request the value for which you want to get the
square root.
val = Application.InputBox( _
prompt:="Please enter the value for which you
want " & _
"to find the square root:", Type:=1)

' Set up the parameters for the model.
SolverOK SetCell:=Range("A2"), MaxMinVal:=3,
ValueOf:=val, _
ByChange:=Range("A1")

' Do not display the Solver Results dialog box.
SolverSolve UserFinish:=True

' Save the value of cell A1 (the changing cell)
before you discard
' the results.
sqroot = Range("a1")

' Finish and discard the results.
SolverFinish KeepFinal:=2

' Show the result in a message box.
MsgBox "The square root of " & val & " is " & Format
(sqroot, "0.00")

End Sub

Bye
Marco