Search code examples
excelvbaadd-insolver

Excel Solver in VBA

I'm attempting to use the the Solver in VBA. I have it working; however, I want the last cell in the ByChange:=Range($E$$: ****) to be the last row with a number (which changes). I'm thinking I need to incorporate a StLastRow, but I never wrote code before and would greatly appreciate the help!

Just for additional background, I have a sheet where I input a pantry inventory (some weeks there's 20 food items, some weeks there's 50). I want the code to choose the ByChanging Range based on the number of items entered. If that makes sense.

Here is the code I have working, but instead $E$9 I want it to look for the last row filled in.

Sub Solver()

SolverReset
SolverOK ByChange:=Range("$E$4:**$E$9**")
SolverAdd cellRef:="$J$3", _
 relation:=2, FormulaText:="$C$5"
SolverAdd cellRef:="$M$3", _
 relation:=2, FormulaText:="$C$6"
SolverAdd cellRef:="$P$3", _
 relation:=2, FormulaText:="$C$7"
SolverAdd cellRef:="$S$3", _
 relation:=2, FormulaText:="$C$8"

SolverSolve UserFinish:=False

End Sub
Solution

  • To get the last row in Column E dynamically, Change your line of:

    SolverOK ByChange:=Range("$E$4:**$E$9**")

    With these line:

    Dim LastRow As Long
Dim sht As Worksheet

' change "Sheet1" to your sheet name
Set sht = ThisWorkbook.Sheets("Sheet1")

' find last row in Column E
LastRow = sht.Cells(sht.Rows.Count, "E").End(xlUp).Row

SolverReset
SolverOK ByChange:=Range("$E$4:$E$" & LastRow)