Amend solver VBA Code

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,229
Office Version
  1. 2021
Platform
  1. Windows
have the following macro for updating values using Solver


File-Copy-icon.png

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

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.

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,229
Office Version
  1. 2021
Platform
  1. Windows
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





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

Forum statistics

Threads
1,181,322
Messages
5,929,287
Members
436,660
Latest member
Mouseinalabyrinth

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