Excel 2010 Looping solver macro

Hello,

I'm relatively new to VB, and to the forum, I'd appreciate your help with the following.

I'm trying to use solver to optimise a value in cell O5 so that it's identical to a value in cell P5 by changing cells E5 & F5. I'm also trying to loop the code so that the sequence is repeated for every subsequent row until row 781.

I've so far tried using the following code, but to no avail:

Please Login or Register  to view this content.

Can anyone point out where I'm going wrong, or give me any pointers please?

At the moment you are running solver with fixed values. I can't see you are using r to update any solver parameter. Could this be what you wish to do?

Please Login or Register  to view this content.

Looping solver using VBA will not work om the MAC version of solver.

There is a serious problem using Solver for Excel in Office 2011 when it is called from vba. Because Solver is a separate application, control must be transferred from Excel to Solver, and this control does not occur until after vba has terminated. This means that any processing that occurs in the vba program after the call to SolverSolve will be completed before Solver has done its task.

Thus a vba program to call solver and then capture or process the results _cannot_ work. It also precludes calling Solver from vba in a loop even though the documentation suggests this is possible

Alf

Maybe ...

Please Login or Register  to view this content.

Hi shg, thanks for your reply.

That works perfectly, but only as far as row 75. After that, the remaining cells come up with a #DIV/0! error.

The solved E75 cell has been changed to 0. That's fine, but is there some way that this is stopping the remaining cells from solving?

Thanks!

What happens at row 75?

I second Alf's suggestion.

Looks to me like col E needs to be constrained to be >0.

Please Login or Register  to view this content.