solver problems

[reaganlh]

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?

 

[SteveM]

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

 

[reaganlh]

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!

 

[reaganlh]

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!

 

[reaganlh]

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?

 

[SteveM]

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

 

[reaganlh]

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