Amend solver VBA Code

[howard]

have the following macro for updating values using Solver

Option Explicit
Sub Solver()

    SolverOk SetCell:="$E$1", MaxMinVal:=3, ValueOf:=72, ByChange:="$E$2:$E$27", _
        Engine:=2, EngineDesc:="Simplex LP"
    SolverOk SetCell:="$E$1", MaxMinVal:=3, ValueOf:=72, ByChange:="$E$2:$E$27", _
        Engine:=2, EngineDesc:="Simplex LP"
    SolverSolve
End Sub

I need the macro amended as follows:


1) I need ValueOf:=72 to refer to cell F1

2) I need $E$$27 to refer to the last cell in Col D in ByChange:="$E$2:$E$27"


Your assistance in this regard is most appreciated

I have also posted on Macro to automate Solver

 

[howard]

I also posted on https://www.myonlinetraininghub.com/excel-forum/vba-macros/automate-solver-using-vba#p21541

 

[howard]

I have figured out all the code, except how to automatically change ValueOf:=72, to refer to cell F1 or its named range Sum_Val


It would be appreciated if someone could assist me

 Option Explicit
Sub UpdateSolver()
Dim LR As Long
LR = Cells(Rows.Count, "A").End(xlUp).Row



    SolverReset
    SolverOk SetCell:="$E$1", MaxMinVal:=3, ValueOf:=72, ByChange:="E2:E" & LR, _
        Engine:=2, EngineDesc:="Simplex LP"
    SolverAdd CellRef:="$E$2:$E$27", Relation:=5, FormulaText:="binary"
    SolverOk SetCell:="$E$1", MaxMinVal:=3, ValueOf:=72, ByChange:="E2:E" & LR, _
        Engine:=2, EngineDesc:="Simplex LP"
       SolverSolve
End Sub