Solver VBA not setting cell references!

  • I've got an enfuriating problem using the Solver in VBA.

    I use a range to hold my "SetCell" by "Varying" values. I then loop over the range rows, resetting the solver each time and setting the cell references to vary etc. then adding a constraint. Finally I run the Solver. Trouble is the cell references are not being set as I loop over the range...

    Can anyone see what I'm doing wrong?

  • Could it be that ref1 and ref2 are Procedure level variables? As you are passing arguments these may need to be Project Level (Public). That is;

    'MUST be placed at the top of any Standard Module.
    Public ref1 As String, ref2 As String

  • Hmm. Your macro seemed to work OK for me. It changed the references appropriately (of course I didn't have your data so just got a bunch of zeros in column O).

    That said, I did have a similar problem a few days ago in which solver would not take binary constraint being added by a macro unless the macro was run twice. I could repeat the problem endlessly, but never did solve it.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!