Using solver
 

Hi,

I have written a macro to calculate a result based on 5 variables and then
uses these variables to analyse a column of values.

I would like to use the solver addin to work out the optimal combination of
these 5 variables. However running the solver does not trigger the macro to
run.
The macro is set so that if any of the 5 variables are changed, the macro is
triggered.

Where am I going wrong.

Any help would be appreciated.

Using solver
 

The Solver add-in (which uses VB) disables the Application.EnableEvents while
it is running, so your event macro is not able to detect the changes.

Possilbe work-around would be to set your macro to go off when sheet is
deactivated, or some other similar event.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Leog" wrote:

Hi,

I have written a macro to calculate a result based on 5 variables and then
uses these variables to analyse a column of values.

I would like to use the solver addin to work out the optimal combination of
these 5 variables. However running the solver does not trigger the macro to
run.
The macro is set so that if any of the 5 variables are changed, the macro is
triggered.

Where am I going wrong.

Any help would be appreciated.

Using solver
 

Thanks for your quick reply.

That explains the problem.
Unfortunately the solver does not seem to change the variables directly
while trialling different variables.

One wasy I can see is to make a formula for each of the cells. Unfortunately
this would be more cumbersome to change.






"Luke M" wrote:

The Solver add-in (which uses VB) disables the Application.EnableEvents while
it is running, so your event macro is not able to detect the changes.

Possilbe work-around would be to set your macro to go off when sheet is
deactivated, or some other similar event.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Leog" wrote:

Hi,

I have written a macro to calculate a result based on 5 variables and then
uses these variables to analyse a column of values.

I would like to use the solver addin to work out the optimal combination of
these 5 variables. However running the solver does not trigger the macro to
run.
The macro is set so that if any of the 5 variables are changed, the macro is
triggered.

Where am I going wrong.

Any help would be appreciated.