Solver VBA - Changing variables in column

tropics123

Board Regular
Joined
May 11, 2016
Messages
85
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
 
Last edited:

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,130
This example works for me:

Code:
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

Board Regular
Joined
May 11, 2016
Messages
85
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

Well-known Member
Joined
Oct 30, 2011
Messages
4,130
  • 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


Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,045
Messages
5,639,746
Members
417,108
Latest member
Thein Than

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top