Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solver Record Macro Function
All:
I am at a loss on how to use Solver in VBA. I have recorded a macro based on my Solver requirements. Once I attempt to run this recorded code, it does nothing. I have spent hours trying to ensure my references were correct and that the syntax is correct. Can someone assist me either via this board or off line. Running Solver manually works perfectly; however, when I attempt to automate this it does nothing. There are no error messages and the target cell never changes. It is as if it is not running at all. I am assuming there are no issues with the spreadsheet, since Solver seems to work just fine when initiated manually. Here is the code: Sub Macro10() Dim ShtOb As Object Worksheets("Pressure Analysis").Activate Set ShtOb = Worksheets("Pressure Analysis") SolverReset SolverOk SetCell:=ShtOb.Cells(54, 15), MaxMinVal:=3, ValueOf:=4215, ByChange:=ShtOb.Cells(64, 15) SolverAdd CellRef:=ShtOb.Cells(64, 15), Relation:=1, FormulaText:=4215 SolverAdd CellRef:=ShtOb.Cells(64, 15), Relation:=3, FormulaText:=250 SolverOk SetCell:=ShtOb.Cells(54, 15), MaxMinVal:=3, ValueOf:=4215, ByChange:=ShtOb.Cells(64, 15) SolverSolve UserFinish:=True End Sub Any suggestions would be greatly appreciated. Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solver Record Macro Function
Just guessing here. Would this apply?
XL2000: The Solver Add-in May Not Work When You Start Solver by Using a Macro http://support.microsoft.com/default...30&Product=xlw (I notice you have two SolverOk statements??) Technique only would be to reverse these two statements. (Less chance for typo errors) Set ShtOb = Worksheets("Pressure Analysis") ShtOb.Activate HTH Dana DeLouis "Floyd Bates" wrote in message ... All: I am at a loss on how to use Solver in VBA. I have recorded a macro based on my Solver requirements. Once I attempt to run this recorded code, it does nothing. I have spent hours trying to ensure my references were correct and that the syntax is correct. Can someone assist me either via this board or off line. Running Solver manually works perfectly; however, when I attempt to automate this it does nothing. There are no error messages and the target cell never changes. It is as if it is not running at all. I am assuming there are no issues with the spreadsheet, since Solver seems to work just fine when initiated manually. Here is the code: Sub Macro10() Dim ShtOb As Object Worksheets("Pressure Analysis").Activate Set ShtOb = Worksheets("Pressure Analysis") SolverReset SolverOk SetCell:=ShtOb.Cells(54, 15), MaxMinVal:=3, ValueOf:=4215, ByChange:=ShtOb.Cells(64, 15) SolverAdd CellRef:=ShtOb.Cells(64, 15), Relation:=1, FormulaText:=4215 SolverAdd CellRef:=ShtOb.Cells(64, 15), Relation:=3, FormulaText:=250 SolverOk SetCell:=ShtOb.Cells(54, 15), MaxMinVal:=3, ValueOf:=4215, ByChange:=ShtOb.Cells(64, 15) SolverSolve UserFinish:=True End Sub Any suggestions would be greatly appreciated. Thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solver Record Macro Function
i have the same problem. did you find a solution and if so, can you tell us?
thank toby "Dana DeLouis" wrote: Just guessing here. Would this apply? XL2000: The Solver Add-in May Not Work When You Start Solver by Using a Macro http://support.microsoft.com/default...30&Product=xlw (I notice you have two SolverOk statements??) Technique only would be to reverse these two statements. (Less chance for typo errors) Set ShtOb = Worksheets("Pressure Analysis") ShtOb.Activate HTH Dana DeLouis "Floyd Bates" wrote in message ... All: I am at a loss on how to use Solver in VBA. I have recorded a macro based on my Solver requirements. Once I attempt to run this recorded code, it does nothing. I have spent hours trying to ensure my references were correct and that the syntax is correct. Can someone assist me either via this board or off line. Running Solver manually works perfectly; however, when I attempt to automate this it does nothing. There are no error messages and the target cell never changes. It is as if it is not running at all. I am assuming there are no issues with the spreadsheet, since Solver seems to work just fine when initiated manually. Here is the code: Sub Macro10() Dim ShtOb As Object Worksheets("Pressure Analysis").Activate Set ShtOb = Worksheets("Pressure Analysis") SolverReset SolverOk SetCell:=ShtOb.Cells(54, 15), MaxMinVal:=3, ValueOf:=4215, ByChange:=ShtOb.Cells(64, 15) SolverAdd CellRef:=ShtOb.Cells(64, 15), Relation:=1, FormulaText:=4215 SolverAdd CellRef:=ShtOb.Cells(64, 15), Relation:=3, FormulaText:=250 SolverOk SetCell:=ShtOb.Cells(54, 15), MaxMinVal:=3, ValueOf:=4215, ByChange:=ShtOb.Cells(64, 15) SolverSolve UserFinish:=True End Sub Any suggestions would be greatly appreciated. Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Record Macro and Edit Macro options disabled | New Users to Excel | |||
What does it mean to record the macro | Excel Discussion (Misc queries) | |||
How to record the EXCEL Solver Parameters Dialog Box? | Excel Discussion (Misc queries) | |||
need a function (not macro with ontime) to record a snapshot of a changing cell | Excel Programming | |||
Calling up the Solver function in a VBA macro | Excel Programming |