Macro to automate Solver

I 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

https://www.mrexcel.com/board/thread...-code.1176711/

https://www.myonlinetraininghub.com/...ing-vba#p21541