is it possible to reference a named range in excel solver constraints?

[breezer123]

Hi all! Thank you for taking the time to read my question and hopefully help me with an answer! In this line "SolverAdd CellRef:="minrange", Relation:=3, FormulaText:="$J$2"" I am trying to constrain the cells in the "minrange" to be greater than J2. However, the current range of values in my spreadsheet has "" cells in the range. I tried to create minrange to have it be all of the non "" cells, or the cells with values in them. When I originally tried to put AN45:AN76 (the full range) in the spot where "minrange" currently is, my solver could not find a feasible solution because blank cells will never be greater than my J2 value. The minrange code could be incorrect, or this tactic may not be possible in solver. I am not sure if anyone knows the answer to this, or if they know how to help me with this solution. Thanks in advance!

Dim minrange As Range, c As Range, i As Long
For Each c In Range("AN45:AN76")
    If c.Value > 0 Then
        Exit For
    End If
Next
Set minrange = c
    For i = minrange.Row + 1 To 76
        If Range("AN" & i).Value > 0 Then
            Set minrange = Union(minrange, Range("AN" & i))
        End If
    Next

SolverOk SetCell:="$AN$79", MaxMinVal:=1, ValueOf:=0, ByChange:="$J$9:$J$39", _
    Engine:=1, EngineDesc:="GRG Nonlinear"
SolverAdd CellRef:="minrange", 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"

 

[i_nth]

You can use range names in Solver - actually, it is preferred as the models are easier to read.
However, the range must be contiguous. You seem to be creating a range that consists of multiple regions - that won't work.
You'll need to collate the cells you want to constrain into a contiguous block, then you can make the constraint.

 

[breezer123]

You can use range names in Solver - actually, it is preferred as the models are easier to read.
However, the range must be contiguous. You seem to be creating a range that consists of multiple regions - that won't work.
You'll need to collate the cells you want to constrain into a contiguous block, then you can make the constraint.

Thank you so much for clarifying! Do you know an easy way for me to make a new column with just the cells with values in them from my previous column? The number of cells with values can't be predefined though, so it would just need to keep filling until there is no more left. Then that would be the cell reference I would have, but even then it wouldnt be a specific range for solver because the number of rows would change. Sorry if that was confusing, do you understand my problem? Thanks for your help!

 

[i_nth]

Can you sort the cells in column AN so the ones you want are at the top? If so, you could then define a range that covers only the non-blank cells and use that in the Solver constraint.