| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Having problems w/ Solver code
I am writing solver code behind an excel spreasheet and am having difficulty
making it work. The code is listed below: Sub signif_control_pop() Dim cntlpc Dim pop 'Clear any previous solver settings. SOLVERReset 'Prompt user for population size. pop = Application.InputBox(Prompt:="Population Total:", Type:=1) Range("C3") = pop 'Set targeted cell, H16, to a value by changing the range of acceptable percentages. SolverOK SetCell:=Range("H16"), MaxMinVal:=3, _ ValueOf:=0.95, _ byChange:=Range("B5") ' Solve the model and keep the final results. SolverSolve Userfinish:=False 'Save results of control percent before you discard the results. cntl_pc = Range("B5") SolverFinish KeepFinal:=1 'NOTE: Does not work if I include ReportArray:=1 ' Show the result in a message box. MsgBox "The Control Population Percent for " & pop & " is " & cntl_pc & " " End Sub The problem I'm having is regarding the "ByChange" cell (B5) used in the solver function. The value in the cell never changes. When the message box is displayed, it always displays the same number, which is the number that was sitting in cell B5 before I ran the program. I have included "SolverFinish KeepFinal:= 1" in the code. If I do not run the visual basic code, but rather manually run Solver in excel, it will change cell as I want it to do. Any help is appreciated. Stacy |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Having problems w/ Solver code
SolverFinish KeepFinal:=1 'NOTE: Does not work if I include
ReportArray:=1 Hi. I don't have the op's original post. Try... ReportArray:=Array(1) Your code "should" work. For debugging your problem, try the following and see what is returned to "Result." Result = SOLVERSOLVE(True) MsgBox Result SOLVERFINISH 1, Array(1) cntl_pc = Range("B5") '...ect -- HTH :) Dana DeLouis Windows XP & Office 2007 "Jon Peltier" wrote in message ... As I answered in another forum, Solver takes a string address, not a range. This should work: SolverOK SetCell:="$H$16", MaxMinVal:=3, _ ValueOf:=0.95, _ byChange:="$B$5" - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Stacy B" <Stacy wrote in message ... I am writing solver code behind an excel spreasheet and am having difficulty making it work. The code is listed below: Sub signif_control_pop() Dim cntlpc Dim pop 'Clear any previous solver settings. SOLVERReset 'Prompt user for population size. pop = Application.InputBox(Prompt:="Population Total:", Type:=1) Range("C3") = pop 'Set targeted cell, H16, to a value by changing the range of acceptable percentages. SolverOK SetCell:=Range("H16"), MaxMinVal:=3, _ ValueOf:=0.95, _ byChange:=Range("B5") ' Solve the model and keep the final results. SolverSolve Userfinish:=False 'Save results of control percent before you discard the results. cntl_pc = Range("B5") SolverFinish KeepFinal:=1 'NOTE: Does not work if I include ReportArray:=1 ' Show the result in a message box. MsgBox "The Control Population Percent for " & pop & " is " & cntl_pc & " " End Sub The problem I'm having is regarding the "ByChange" cell (B5) used in the solver function. The value in the cell never changes. When the message box is displayed, it always displays the same number, which is the number that was sitting in cell B5 before I ran the program. I have included "SolverFinish KeepFinal:= 1" in the code. If I do not run the visual basic code, but rather manually run Solver in excel, it will change cell as I want it to do. Any help is appreciated. Stacy |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Having problems w/ Solver code
Thanks for your help. I've tried this but it still doesn't work. Solver
doesn't appear to work at all, but since my code does not return any errors, I cannot figure this out. Everything seems fine in my References. Any further help is appreciated. "Jon Peltier" wrote: As I answered in another forum, Solver takes a string address, not a range. This should work: SolverOK SetCell:="$H$16", MaxMinVal:=3, _ ValueOf:=0.95, _ byChange:="$B$5" - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Stacy B" <Stacy wrote in message ... I am writing solver code behind an excel spreasheet and am having difficulty making it work. The code is listed below: Sub signif_control_pop() Dim cntlpc Dim pop 'Clear any previous solver settings. SOLVERReset 'Prompt user for population size. pop = Application.InputBox(Prompt:="Population Total:", Type:=1) Range("C3") = pop 'Set targeted cell, H16, to a value by changing the range of acceptable percentages. SolverOK SetCell:=Range("H16"), MaxMinVal:=3, _ ValueOf:=0.95, _ byChange:=Range("B5") ' Solve the model and keep the final results. SolverSolve Userfinish:=False 'Save results of control percent before you discard the results. cntl_pc = Range("B5") SolverFinish KeepFinal:=1 'NOTE: Does not work if I include ReportArray:=1 ' Show the result in a message box. MsgBox "The Control Population Percent for " & pop & " is " & cntl_pc & " " End Sub The problem I'm having is regarding the "ByChange" cell (B5) used in the solver function. The value in the cell never changes. When the message box is displayed, it always displays the same number, which is the number that was sitting in cell B5 before I ran the program. I have included "SolverFinish KeepFinal:= 1" in the code. If I do not run the visual basic code, but rather manually run Solver in excel, it will change cell as I want it to do. Any help is appreciated. Stacy |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Solver problems | Excel Worksheet Functions | |||
| Solver Problems | Excel Worksheet Functions | |||
| Problems with solver and vba | Excel Programming | |||
| More Solver Problems | Excel Programming | |||
Linear Mode
