SOLVER VBA making range dynamic

[vbanovice1992]

Hi Guys I am using solver to identify from multiply amounts which add up to a specific number. I would like to make the "By change " to a dynamic range that will work no matter how many values are entered . I've been trying this for now 3 weeks and would appreciate someone helping me with the code here is the code I have".


SolverOk SetCell:="$F$6", MaxMinVal:=3, ValueOf:=0, ByChange:="$B$5:$B$16", _
Engine:=2, EngineDesc:="Simplex LP"
SolverOk SetCell:="$F$6", MaxMinVal:=3, ValueOf:=0, ByChange:="$B$5:$B$16", _
Engine:=2, EngineDesc:="Simplex LP"
SolverSolve

 

[mrshl9898]

Try setting a range to use:

Sub test()

Dim sht As Worksheet
Dim myrange As Range
Dim LastRow As Long

Set sht = ActiveSheet

LastRow = sht.Cells(sht.Rows.Count, "C").End(xlUp).Row

Set myrange = Range("B5:$B" & LastRow)


SolverOk SetCell:="$F$6", MaxMinVal:=3, ValueOf:=0, ByChange:=myrange, _
Engine:=2, EngineDesc:="Simplex LP"

End Sub

 

[vbanovice1992]

I am getting this error?

 

[vbanovice1992]

and this is whats happening