Interacting with EXCEL Solver via macro

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

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

>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
>
>
>.
>

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

"Markus Scheible" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>>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
>>
>>
>>.
>>

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

In article <[email protected]>, Whooshbopbang4
@adelphia.net says...
> 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
>
> "Markus Scheible" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >>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
> >>
> >>
> >>.
> >>
>
>
>

Thank you very much

"Tushar Mehta" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> In article <[email protected]>, Whooshbopbang4
> @adelphia.net says...
>> 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
>>
>> "Markus Scheible" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> >
>> >>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
>> >>
>> >>
>> >>.
>> >>
>>
>>
>>

In article <[email protected]>, Whooshbopbang4
@adelphia.net says...
> 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