Optimization with solver

Hello,
For my optimization problem I use solver function which parametars are changed depending of some data is checked or not for calculation.
My problem is how to define parameter ByChange:= in solver function
I tried to use array of range but that doesn't work
Here is my code

For a = 1 To 24
    SolverReset
    c = ws.Range("d11")
    j = 0 
    For b = 1 To 12
        If c = True Then
            j = j + 1
            d = ws.Range("F" & 10 + b)
            e = ws.Range("g" & 10 + b)
            f = ws.Range("ag" & 10 + b)
            If f < d Then
                ws.Range("d" & 11 + b) = False
            Else
                If f < e Then
                    ws.Range("g" & 10 + b) = f
                End If
            End If
            Set I(j) = Range("H" & b + 10)
            SolverAdd CellRef:=ws.Cells(10 + b, a + 7), Relation:=3, FormulaText:=d
            SolverAdd CellRef:=ws.Cells(10 + b, a + 7), Relation:=1, FormulaText:=e
        End If
        c = ws.Range("d" & 11 + b)
    Next
    
    SolverAdd CellRef:=ws.Cells(23, a + 7), Relation:=2, FormulaText:=ws.Cells(10, a + 7)
    SolverOk SetCell:=Cells(27, 35 + a), MaxMinVal:=2, ValueOf:="0", ByChange:=I
    SolverSolve userFinish:=True
Next
End Sub

Please help

Source Excel VB help:

SolverOk Function

Before you use this function, you must establish a reference to the Solver add-in. With a Visual Basic module active, click References on the Tools menu, and then select the Solver.xla check box under Available References. If Solver.xla doesn't appear under Available References, click Browse and open Solver.xla in the \Office\Library\Solver\ subfolder.

SolverOk(SetCell, MaxMinVal, ValueOf, ByChange)

SetCell Optional Variant. Refers to a single cell on the active worksheet. Corresponds to the Set Target Cell box in the Solver Parameters dialog box.

MaxMinVal Optional Variant. Corresponds to the Max, Min, and Value options in the Solver Parameters dialog box.

MaxMinVal Specifies
1 Maximize.
2 Minimize.
3 Match a specific value.


ValueOf Optional Variant. If MaxMinVal is 3, you must specify the value to which the target cell is matched.

ByChange Optional Variant. The cell or range of cells that will be changed so that you’ll obtain the desired result in the target cell. Corresponds to the By Changing Cells box in the Solver Parameters dialog box.

Thanks for your answer but maybe I wasn't to clear with my queastion. Slover function is working in my code but only if I specifie for ex. ByChange="a5" etc

But what I wanted to do is array of a range ( I(j) in my code) which I put in function solver as an argument for (ByChange:=I)

Is that possible?

try this way:

I(j)="H" & b + 10
...
ByChange:=Join(I, ",")

Originally Posted by watersev

try this way:

I(j)="H" & b + 10
...
ByChange:=Join(I, ",")

nope, doesn't work
I created a string with all names of cells which I need in my range and then put it as a argument for solver function

Problem solved

BR

Hi Homeboy_8

” I created a string with all names of cells which I need in my range and then put it as an argument for solver function

Problem solved”

As I’m most interested to learn could you please explain your solution in a bit more detail.

And even if you solved your problem yourself, could you still mark this post as “Solved” this may help somebody with a similar problem.

Regards

Alf