CODEWITHSUNDEEP
excelvba
user2864747
user2864747

Reputation: 1

Inserting conditional cells into the Solver "byChange" section

I am looking to insert cells into the "bychange" section of the solver macro and I can't figure out how to add all the cells. I tried it with one by defining a range and then using range.address, but it doesn't work when I try to expand it and use more cells (by way of defining a range) in the by change section.

Sub Solver_button()
' Nominates ranges that will be used in the solver
Dim C_col As Range
Dim D_col As Range
Dim E_col As Range
Dim F_col As Range
Dim G_col As Range
Dim H_col as Range

On Error Resume Next
If range("$c$4").Value = "yes" Then C_col = range("$C$7")
If range("$d$4").Value = "yes" Then D_col = range("$D$7")
If range("$e$4").Value = "yes" Then E_col = range("$e$7")
If range("$f$4").Value = "yes" Then F_col = range("$f$7")
If range("$g$4").Value = "yes" Then G_col = range("$g$7")
If range("$h$4").Value = "yes" Then H_col = range("$h$7")

SolverOk SetCell:="$O$9", MaxMinVal:=2, ValueOf:=0, ByChange:=[c_col.address,d_col.address,e_col.address,f_col.address,g_col.address,h_col.address],
    Engine:=1, EngineDesc:="GRG Nonlinear"
SolverSolve

Upvotes: 0

Views: 468

Answers (1)

Tim Williams
Tim Williams

Reputation: 166241

Untested:

Sub Solver_button()
' Nominates ranges that will be used in the solver
Dim rngChange As Range, c As Range

    For Each c In Range("C4:H4").Cells
        If c.Value = "yes" Then
            If rngChange Is Nothing Then
                Set rngChange = c.Offset(3, 0)
            Else
                Set rngChange = Application.Union(rngChange, _
                                           c.Offset(3, 0))
            End If
        End If
    Next c

    If Not rngChange Is Nothing Then
        SolverOk SetCell:="$O$9", MaxMinVal:=2, ValueOf:=0, _
           ByChange:=rngChange.Address(), Engine:=1, _
           EngineDesc:="GRG Nonlinear"
        'SolverSolve
    End If

End Sub

Upvotes: 1

Related Questions