Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interacting with EXCEL Solver via macro
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interacting with EXCEL Solver via macro
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" wrote in message ... 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interacting with EXCEL Solver via macro
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 , 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" wrote in message ... 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interacting with EXCEL Solver via macro
Thank you very much
"Tushar Mehta" wrote in message ... 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 , 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" wrote in message ... 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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interacting with EXCEL Solver via macro
In article , 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Interacting with 2 different excel files | Excel Discussion (Misc queries) | |||
Problem interacting between VB.NET and VBA6 with Excel 2003 | Excel Discussion (Misc queries) | |||
excel solver macro | Excel Programming | |||
macro using excel solver | Excel Programming | |||
Interacting outside Excel | Excel Programming |