Solver VBA - Changing variables in column

[tropics123]

Hi, looking for some help with this Solver VBA. Here's what I'm trying to do:

1) "Set Objective" is equal to the value in cell D3
2) "Value of" is equal to the value in cell D1
3) "By Changing Variable Cells" - this range in column D (starts on D4) changes according to the number of rows in column B
4) Solver results should only return integers in column D (I set the constraint = integer). This constraint range also changes when the "Changing Variable Cells" change.

Problem: I think the code for the changing range in column D is causing the issue but I don't know what to do to make it work.


Sheets("Sheet3").Select


SolverOptions MaxTime:=0, Iterations:=0, Precision:=0.0000000001, Convergence:= _
0.0001, StepThru:=False, Scaling:=True, AssumeNonNeg:=True, Derivatives:=1
SolverOptions PopulationSize:=100, RandomSeed:=0, MutationRate:=0.075, Multistart _
:=False, RequireBounds:=True, MaxSubproblems:=0, MaxIntegerSols:=0, _
IntTolerance:=1, SolveWithout:=False, MaxTimeNoImp:=30

Dim Lr As Long
Lr = .Cells(.Rows.Count, 3).End(xlUp).Row 'Changing variables in column D by counting the last row in column C

SolverOk SetCell:="$D$3", MaxMinVal:=3, ValueOf:="$D$1", ByChange:= _
Range("D4:D" & Lr), Engine:=1, EngineDesc:="GRG Nonlinear"
SolverAdd CellRef:=Range("$D4:D" & Lr), Relation:=4, FormulaText:="integer"

SolverSolve

End With

End Sub

 

[Worf]

This example works for me:

Sub Macro1()


Dim lr%
lr = Range("d" & Rows.Count).End(xlUp).Row


SolverOk SetCell:="$D$" & lr, MaxMinVal:=1, ValueOf:=0, _
ByChange:="$B$3:$B$" & lr - 1, Engine:=2, EngineDesc:="Simplex LP"
  
SolverSolve
End Sub

 

[tropics123]

Hi Worf, the "SetCell" and "ValueOf" needs to read value from cells D3 and D1, respectively. The values in those cells will change regularly depending on the data we received. The issue I had was getting the VBA to read from those cells. For example, if I take your code and change it to the following, it doesn't work.

Dim lr%
lr = Range("d" & Rows.Count).End(xlUp).Row


SolverOk SetCell:="$D$3" & lr, MaxMinVal:=1, ValueOf:="$D$1", _
ByChange:="$B$3:$B$" & lr - 1, Engine:=2, EngineDesc:="Simplex LP"

SolverSolve
End Sub

 

[Worf]

• What do you mean by does not work? Do you get an error?
• The code below works for me.
• Obviously, my test model is different from yours. Can you email me your problem?
• If you want cell D3, use “$d$3”, not "$D$3" & lr

Sub Macro1()


Dim lr%
lr = Range("d" & Rows.Count).End(xlUp).Row - 1


SolverOk SetCell:="$D$6", MaxMinVal:=1, ValueOf:="$e$2", _
ByChange:="$B$3:$B$" & lr, Engine:=2, EngineDesc:="Simplex LP"
SolverSolve
End Sub