VBA Solver - Multiple ranges of variables and constraints

[DOtjacq1]

Hello All,

I am using VBA in order to automatize my Solver on multiple problems.

I want to run the solver taking only the range of parameters, constraints and decision variables for which the value is non-zero.

And I want to related to theses ranges created in the solver.

Here is part of my code referring to this idea:

Dim my_rangeDecisonVariableALL As Range
    For Each cell In rngObjectCell.Offset(0, -363).Range("A1:CL1")
        If cell.Value <> 0 Then
            If my_rangeDecisonVariableALL Is Nothing Then
                Set my_rangeDecisonVariableALL = cell
            Else
                Set my_rangeDecisonVariableALL = Union(my_rangeDecisonVariableALL, cell)
            End If
        End If
    Next
    'my_rangeDecisonVariableALL.Select
            
    Dim my_rangeDecisonVariableNeedle As Range
    For Each cell In rngObjectCell.Offset(0, -363).Range("A1:CK1")
        If cell.Value <> 0 Then
            If my_rangeDecisonVariableNeedle Is Nothing Then
                Set my_rangeDecisonVariableNeedle = cell
            Else
                Set my_rangeDecisonVariableNeedle = Union(my_rangeDecisonVariableNeedle, cell)
            End If
        End If
    Next
    'my_rangeDecisonVariableNeedle.Select
            
        For i = 1 To 2
            'Loop to solve the problem twice with the same starting point, ensuring reaching at least one local optimum
            SolverReset
            'Reset the solver for the second run keeping data of the first run
                                   
            SolverOk SetCell:=rngObjectCell.Address, MaxMinVal:=2, ValueOf:=0, ByChange:=my_rangeDecisonVariableALL.Address, _
            Engine:=1, EngineDesc:="GRG Nonlinear"
            SolverAdd CellRef:=my_rangeDecisonVariableNeedle.Address, Relation:=1, FormulaText:=rngObjectCell.Offset(0, -274).Address
            SolverAdd CellRef:=my_rangeDecisonVariableALL.Address, Relation:=1, FormulaText:="25"
            SolverAdd CellRef:=my_rangeDecisonVariableNeedle.Address, Relation:=3, FormulaText:="0"
            SolverAdd CellRef:=my_rangeDecisonVariableNeedle.Address, Relation:=1, FormulaText:=my_rangeDemand_dij.Address
            SolverAdd CellRef:=my_rangeDecisonVariableNeedle.Address, Relation:=4, FormulaText:="integer"
            SolverAdd CellRef:=my_rangeQY_qij_Yij.Address, Relation:=2, FormulaText:=my_rangeDemand_dij.Address
            SolverAdd CellRef:=rngObjectCell.Offset(0, -274).Address, Relation:=3, FormulaText:="0"
            SolverAdd CellRef:=rngObjectCell.Offset(0, -274).Address, Relation:=1, FormulaText:=rngObjectCell.Offset(0, -455).Address
            SolverAdd CellRef:=rngObjectCell.Offset(0, -274).Address, Relation:=4, FormulaText:="integer"
            SolverAdd CellRef:=rngObjectCell.Offset(0, -182).Address, Relation:=2, FormulaText:=rngObjectCell.Offset(0, -455).Address
            SolverAdd CellRef:=rngObjectCell.Offset(0, -182).Address, Relation:=2, FormulaText:=rngObjectCell.Offset(0, -184).Address
            SolverOk SetCell:=rngObjectCell.Address, MaxMinVal:=2, ValueOf:=0, ByChange:=my_rangeDecisonVariableALL.Address, _
            Engine:=1, EngineDesc:="GRG Nonlinear"
            SolverOk SetCell:=rngObjectCell.Address, MaxMinVal:=2, ValueOf:=0, ByChange:=my_rangeDecisonVariableALL.Address, _
            Engine:=1, EngineDesc:="GRG Nonlinear"
            SolverOk SetCell:=rngObjectCell.Address, MaxMinVal:=2, ValueOf:=0, ByChange:=my_rangeDecisonVariableALL.Address, _
            Engine:=1, EngineDesc:="GRG Nonlinear"
            SolverSolve (True)
    
            SolverSolve userFinish:=True
            SolverFinish KeepFinal:=1
    
        Next i

I have two problems:

- The way I am building a new range of the non-zero value from another range is not working perfectly, it does not put the non-zero value of a column when this non-zero value is preceded by another non-zero value

- The model does take into account the new range defined for the decision variable (after the ByChange:=) but it does not work for the constraint defined with such a new range


Does anyone could help me with this request please ?

Thank you

Damien