MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VB code for solver


Posted by Dan on August 06, 2000 10:07 PM

I have a spreadsheet in which I require multiple uses of the solver
add-in (approx 100 repitions). If I write a macro (shown below) to run solver on the numerous different cells, the "solver
results" window pops up for each time solver is run.

For a = 1 To 300 Step 3
solverOK SetCell:=Range("$E$" & 115 + a & ""), _
MaxMinVal:=3, _
ValueOf:="0", _
ByChange:=Range("$C$" & 115 + a & "")
SolverSolve
Next a

I wish to elimate this pop-up so that the solver solution is automatically saved each time. I firstly tried to by-pass this
problem by writing some VB code to simulate the solver but was unable to effectively simulate the precision and tolerance
options of solver, without using excessive lines of code. I have attempted what I think to be all options to solve this and have been unsuccesful. I know that it
will probably end up being a simple one line of code that will solve this problem and would greatly appreciate any
help/input you could provide!!

Cheers!!

Dan


Posted by Michael Liu on August 07, 0100 2:39 PM

I have used VB to invoke solver iteratively for
some financial optimizations. Here's the tail
end of my looped solving code:

SolverSolve userFinish:=True
SolverFinish

This should let you run through each iteration without
the popup box coming up (unless it finds no solution =) ).

Cheers!
Mike