cannot select cells when using solver in excel for mac

Cannot Select Cells When Using Solver In Excel Code Or Interface

I have a number of products that need to be loaded into containers if all products dont fit into 1 container add another container and try again and if two dont solve the problem test with 3 then 4 and so forth.

Basically the idea is that I have a known end quantity that has to be roughly (but not necessarily exactly) matched and another value that has to be exactly matched.

B51 and B53 are the bychange cells that Solver should change in order to attain the target value.

Cannot Select Cells When Using Solver In Excel Code Or Interface

Application.displayalerts False Solversolve True, showref:showtrial This is a non-linear model and above is only an excerpt of the code (unfortunately due to data sensitivity and confidentiality linked to my job I cannot post the code or interface in greater detail, but I can provide examples of what I am hypothetically trying to achieve).

My problem is that the by change cells are not changing automatically (though solver is running and producing pop-ups informing me of whether the process has run successfully or otherwise), and the process only works if the by change cells are set manually to near the right number needed for the process to run successfully.

Is there a way in which Solver can be set to run continuously until a solution is found with the cells changing I read somewhere that the problems I am encountering could be because the bychange cells contain too many decimal places.

Is there a way to influence how many decimal places the suggested values are set to.

Please let me know if any further information from me is required.

Thanks.

How close this initial guess needs to be in order for that algorithm to converge on a given solution depends greatly on the nature of the functionproblem you are trying to solve.

Since you have not been able to post details of the problem, we can only speak in generalities.

It is not an oft discussed aspect of these kind of problems, but how to select a good initial guess or seed value can be an important consideration in programming something like this.

When Solver fails to converge or converges on the wrong answer, often times selecting a better initial guess is what is needed to get to the correct solution.

However, it is a difficult question to answer in a generic sense, because it will be strongly dependent on the behavior of your specific functionproblem.

The predicament that I am facing is that the changing values, when set to something completely outside the parameters required to find an optimal solution to the problem, dont change sufficiently enough in the first iteration.

Is there a way in which I can say, for example, that if the 1st generated solution output fails to converge that those values should be used as a base to run the process again and again until the solution is reached The reason for this is that I am attempting to build a forecasting tool that will be used as template from which future predictions can be made without end user intervention.

I have access to Excel 2010 at work so I will attempt to try your suggestion, Alf.

The basic loop would look something like: a) Select initial guess b) Run Solver c) If by changing values are changing too slow, then select new initial guess and return to b d) repeat b and c until a good initial guess is found and Solver converges on a suitable solution.

The difficult part of this kind of algorithm is buried in step c.

Exactly how do you want VBA to select a new initial guess The best way to do this will depend mostly on the nature of the problem where you, the programmer, need to know enough about how the function depends on the initial guess to come up with a suitable way to select initial guesses.

In order to use the macro FillContainer a reference must be set to solver in VBA.

To set a reference to Solver go to - Tools - Macro - Visual Basic Editor.

In the new window that opens go to Tools - References and find box marked Solver.

If Solver box not found click Browse and search for solver normally found under Program FilesMicrosoft OfficeOffice11LibrarySolverSolver.xla (Excel 2003) For Excel 2010 the path is Program FilesMicrosoft OfficeOffice14LibrarySolverSolver.xlam Run macro and after 4 - 5 seconds press Esc button.

After 4 - 5 seconds press Esc button and stop again and keep on doing that till solver finds a solution.

Cannot Select Cells When Using Solver In Excel For Mac