breezer123
New Member
 Joined
 Jul 13, 2020
 Messages
 18
 Office Version

 365
 Platform

 Windows
Hi all! Thank you for taking the time to read my question and potentially offer some help!
I am trying to use solver for an optimization problem. One of my constraints is that the cells in this range must be greater than or equal to an input cell value. However, I want the range to not include the cells that are zero, as this makes the constraint non feasible in solver. My range AN45:AN76 is changing as the solver is running. However, some of the values in the range are zero (or I can easily make them "" if this helps with the solution). The values that are zero are not always the same ones in the column, so I need to find a way to call the range to be all the cells except for the zero ones. My attempt is in this line: minrange = Range(isnot(IsEmpty("$AN$45:$AN$76"))). Thank you in advance for any advice!
I am trying to use solver for an optimization problem. One of my constraints is that the cells in this range must be greater than or equal to an input cell value. However, I want the range to not include the cells that are zero, as this makes the constraint non feasible in solver. My range AN45:AN76 is changing as the solver is running. However, some of the values in the range are zero (or I can easily make them "" if this helps with the solution). The values that are zero are not always the same ones in the column, so I need to find a way to call the range to be all the cells except for the zero ones. My attempt is in this line: minrange = Range(isnot(IsEmpty("$AN$45:$AN$76"))). Thank you in advance for any advice!
VBA Code:
Sub solvermacro()
Application.ScreenUpdating = False
Application.ThisWorkbook.Sheets("Calculations").Activate
Dim minrange As Range
minrange = Range(isnot(IsEmpty("$AN$45:$AN$76")))
SolverOk SetCell:="$AN$79", MaxMinVal:=1, ValueOf:=0, ByChange:="$J$9:$J$39", _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverAdd CellRef:="$AN$45:$AN$76", Relation:=3, FormulaText:="$J$2"
SolverAdd CellRef:="$J$9:$J$39", Relation:=1, FormulaText:="1.25"
SolverAdd CellRef:="$J$9:$J$39", Relation:=3, FormulaText:="0.75"
SolverOptions Iterations:=10, Precision:=0.1, Convergence:= _
0.001, StepThru:=False, Scaling:=False, AssumeNonNeg:=True, Derivatives:=1
SolverOptions PopulationSize:=0, RandomSeed:=0, MutationRate:=0.075, Multistart:= _
False, RequireBounds:=False, MaxSubproblems:=0, MaxIntegerSols:=0, IntTolerance _
:=1, SolveWithout:=False, MaxTimeNoImp:=30
solversolve
End Sub