EXCEL VBA ignores Solver changes values

Question

I have the following snippet of Excel VBA code to solve a minimisation problem:

SolverOk SetCell:="$AP$13", MaxMinVal:=2, ValueOf:=0, ByChange:="$AP$9:$AP$11", Engine:=1
SolverSolve UserFinish:=True

This was created using the "record macro" menu command in Excel. If I run the problem from the menu bar using Data | Solver then Solver happily changes all three cells and completes the problem. The same happens if I run the macro from EXCEL (using Alt-F8). However, I have assigned the same macro to a VBA button and when I press this Excel only changes cell A9. I get the same issue if I change the code to

SolverOk SetCell:="$AP$13", MaxMinVal:=2, ValueOf:=0, ByChange:="$AP$10:$AP$11", Engine:=1

I tried resetting the problem using SolverReset but this produces an error saying Excel has been exhausted.

Would appreciate any suggestions on how to force Excel to change all three cells, not just cell A9

Thanks!

PetGriffin · Accepted Answer · 2018-05-03 09:31:52Z

0

This problem has been modified and updated here:

EXCEL VBA does not run SOLVER inside a macro invoked from a chart button

to avoid problems with chameleon questions

answered May 3, 2018 at 9:31

PetGriffin

5451 gold badge7 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

EXCEL VBA ignores Solver changes values

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related