Interacting with EXCEL Solver via macro

[Dean]

I want to create a macro that will invoke EXCEL's Solver. All I am doing is
setting a target cell (always in the same location) to a value by changing
another cell, which is always in the same location. I know how to write
macros mostly by recording. All that I need to do is record such a sequence.
But, then, since the number I will be setting the target cell to will
change, I need a way for the macro to know to let me enter a value, and
then, I guess, let me hit enter, which should then allow the macro to finish
up with the solver, by accepting the solution.

Can someone tell me what I have to do? Here is my recorded macro. assuming
that, this time, the target was too be set to 1,922,750

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 1/22/2005 by Dean
'

'
SolverOk SetCell:="$M$62", MaxMinVal:=3, ValueOf:="1922750",
ByChange:="$I$62"
SolverSolve
End Sub

Though the above request would be good enough, the "value of" that I will
want to set it to is always the result of a formula that is in another
specific cell, one to the right of the target cell, in this case, cell
$N$62. If there was a way to have the macro find that formula's value and
type that formula's value into the solver "value of" box itself, that would
be even better, as there would be no manual intervention.

Can anyone help me with the first, if not (even better) the 2nd approach?

Thanks much!
Dean

 

[Markus Scheible]

Hi Dean,

SolverOk SetCell:="$M$62", MaxMinVal:=3, ValueOf:="1922750",
ByChange:="$I$62"
SolverSolve
End Sub

try using ValueOf:=range("N62").Value

this should get the content of cell N62 as the valueof
from SOLVER...

Have a nice day!

Markus

 

[Dean]

Thanks. that was too easy. However, before I even tried the change, I could
not find the workbook where I had recorded the macro. So, I recorded it
again. But when I tried to run it, with or without your change, (BTW, I'm
not sure I ever even tried to run it before I asked my question), I got an
error message that said "sub or function not defined", and it highlights the
SolverOK at the beginning of the macro, as if that is where it first
encountered a problem. I have Option Explicit at the top of the sheet. Do
I have to somehow declare this macro, or the Solver functionality?

Anyway, here is the macro that is crashing:

Sub yrtwosolve()
'
' yr2solve Macro
' Macro recorded 1/24/2005 by Dean
'

'
SolverOk SetCell:="$M$62", MaxMinVal:=3, ValueOf:=Range("N62").Value,
ByChange:="$I$162"
SolverSolve
End Sub

Thanks,
Dean

 

[Tushar Mehta]

You need to create a reference to the Solver add-in.

In XL VBA help, search for solverOK, then click the 'SolverOK
Function' The 2nd paragraph tells you want you need to do.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Dean]

Thank you very much

You need to create a reference to the Solver add-in.

In XL VBA help, search for solverOK, then click the 'SolverOK
Function' The 2nd paragraph tells you want you need to do.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Tushar Mehta]

Thank you very much

You are welcome.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions