Thread: Macro to loop solver

Hi all,

I've created an optimization macro that is designed to select a certain number (10 in this case) of control sites for each test site by minimizing a correlation variable between the selected control and test site. To give you some idea of how the problem looks, I've got a pool of around 1400 control sites going down column B, and several hundred test sites going across row 8, with binary variables starting in column C, row 9 and continuing down to row 1409 and across several hundred columns:

Control Sites__Test Sites ----------->
______|______Binary Variables ----------->
______|____________|
______|____________|
______|____________|
______|____________|
_____V___________V

So for each column starting with C, there's a test site that needs 10 control sites selected from a pool of 1400--obviously a very large problem, so I've tried to automate the process by creating a solver loop:

Sub MacroSolve()
Dim Response As Integer
    Response = MsgBox("The control selection may take several hours.  Continue?", vbYesNo + vbExclamation)
    If Response <> vbYes Then Exit Sub
    
Worksheets("OPTIMIZATION MODEL").Activate
ColumnCount = 3
Do While Not IsEmpty(Worksheets("OPTIMIZATION MODEL").Cells(5, ColumnCount))
    SolverReset
    SolverOptions precision:=0.001
    SolverOk SetCell:=Cells(5, ColumnCount), _
        MaxMinVal:=2, _
        ValueOf:="0", _
        ByChange:=Range(Cells(9, ColumnCount), Cells(1409, ColumnCount))
    SolverAdd CellRef:=Cells(1412, ColumnCount), Relation:=2, _
        FormulaText:=Cells(1414, ColumnCount)
    SolverAdd CellRef:=Range(Cells(9, ColumnCount), Cells(1409, ColumnCount)), Relation:=5
    SolverSolve userFinish:=True
    SolverFinish keepFinal:=1
    ColumnCount = ColumnCount + 1
Loop
MsgBox "Control Successfully Selected"

End Sub

Unfortunately the macro doesn't seem to be changing any variables and simply sits and thinks for several hours before telling me it's finished without actually having done anything. Since I'm a relative novice with macros, it may be something simple I'm leaving out, but any help would be greatly appreciated.

Welcome to the forum.

You have 1401 binary cells that you want Solver to evaluate to minimize the target cell -- that's ~ 10^420 combinations. Consider that if you packed protons like billiard balls into a cube the size of the universe, you'd only have about 10^123 of them.

I think the few hours that Solver spends is just meditating on the unsolvable enormity.

With a changing cells as continuous variables, Solver can calculate the partial derivatives of their effect on the target cell in iterating to a solution. I expect with binary values and a non-linear problem, it's less closed-loop.

What does Frontline say?