Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
solver problems
Right, I currently have a main summary sheet on my second sheet in a
workbook, on this sheet I have a few buttons that will run a number of different optimizers. One of these optimizers happens to be solver so when the button is clicked solver is launched and will then run an optimization on sheet1 but for some reason this doesn't seem to work... I read somewhere that you can't run an optimization on a different sheet as to the one solver was launched so I thought I would work my way around this be making sheet one the active sheet...? now I could run the optimization on sheet to with a fornula on sheet one pointing to sheet 2 but this is sucha conveluted mess! Doesn anyone know a way around this? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
solver problems
You can hide the mess by selecting sheet 1 prior to running the
optimization but first insert Application.ScreenUpdating = False before you move to sheet 1. That will maintain the sheet 2 view during the optimization. set it to True after you set focus on sheet 2 again. Something like this: Sub Optimize Application.ScreenUpdating = False Sheets("Sheet 1").Select SolverSolve UserFinish:=True Sheets("Sheet 2").Select Application.ScreenUpdating = True End Sub You have to add the Solver dll as a reference to your project (VB Menu References...) for VB to recognize the Solver functions. Check Help for more about SolverSolve. You can also capture the Solver formulation using the Macro recorder and then modify the code to point to the proper ranges in your formulation. That takes more work, but it gives you more flexibility. SteveM On Nov 11, 9:49 am, wrote: Right, I currently have a main summary sheet on my second sheet in a workbook, on this sheet I have a few buttons that will run a number of different optimizers. One of these optimizers happens to be solver so when the button is clicked solver is launched and will then run an optimization on sheet1 but for some reason this doesn't seem to work... I read somewhere that you can't run an optimization on a different sheet as to the one solver was launched so I thought I would work my way around this be making sheet one the active sheet...? now I could run the optimization on sheet to with a fornula on sheet one pointing to sheet 2 but this is sucha conveluted mess! Doesn anyone know a way around this? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
solver problems
Thanks for that, I was gave that a go and for some odd reason it still
flicks to screen1 and I can see the number being put in and then jumps back to screen 2 :( any ideas? Also thanks for the speedy reply! On a sunday too! ;) On 11 Nov, 15:12, SteveM wrote: You can hide the mess by selecting sheet 1 prior to running the optimization but first insert Application.ScreenUpdating = False before you move to sheet 1. That will maintain the sheet 2 view during the optimization. set it to True after you set focus on sheet 2 again. Something like this: Sub Optimize Application.ScreenUpdating = False Sheets("Sheet 1").Select SolverSolve UserFinish:=True Sheets("Sheet 2").Select Application.ScreenUpdating = True End Sub You have to add theSolverdll as a reference to your project (VB Menu References...) for VB to recognize theSolverfunctions. Check Help for more about SolverSolve. You can also capture theSolver formulation using the Macro recorder and then modify the code to point to the proper ranges in your formulation. That takes more work, but it gives you more flexibility. SteveM |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
solver problems
wait I got it working now
I removed the Application.Run "solver.xla!auto_open" and SolverFinish KeepFinal:=1 seems to be one of those that are messin it up ;) WOOT! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
solver problems
OK as mentioned above I get everything working great until I noticed
that my buttons are doing odd things! First off the button resized itself and now it;s resizing the text inside! any ideas on what would be causing this? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
solver problems
If you are using a Control (not Form) CommandButton object. Right
click in Edit mode and select Format Control and the Properties tab. Then check/adjust the Object Positioning property. SteveM On Nov 12, 4:34 am, wrote: OK as mentioned above I get everything working great until I noticed that my buttons are doing odd things! First off the button resized itself and now it;s resizing the text inside! any ideas on what would be causing this? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
solver problems
thanks for that but it was actually just excel freaking out a tad... I
reset my PC and now it all works good but I have a brand new problem Im afraid. The code below basically creates a few constraints for solver but for some reason I can't get the 3 line with arrows to the left to work... I get the canstraints all set to zero or they are set to the cell before that but never thetwain shall meet... thanks If CheckBox1.Value = True Then total = TextBox1 Sheet1.Cells(116, 3) = TextBox1 For chkcount = 0 To Sheet2.Cells(20, 9) - 1 Sheet1.Cells(106, 3 + chkcount) = 1 'solverchange cellref:=Sheet1.Cells(106, 4 + chkcount), relation:=3, formulatext:=0 If chkcount = 0 Then solveradd cellref:=Sheet1.Cells(106, 4 + chkcount), relation:=1, formulatext:=100 / TextBox1 'solverchange cellref:=Sheet1.Cells(106, 3 + chkcount), relation:=1, formulatext:=100 / TextBox1 Else solveradd cellref:=Sheet1.Cells(106, 4 + chkcount), relation:=1, formulatext:=Sheet1.Cells(106, 4 + chkcount - 1) <------------------------------------- solverchange cellref:=Sheet1.Cells(106, 4 + chkcount), relation:=1, formulatext:=Sheet1.Cells(106, 4 + chkcount - 1)<------------------------------------- End If solveradd cellref:=Sheet1.Cells(106, 4 + chkcount), relation:=3, formulatext:=0<------------------------------------- Next chkcount |
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 |