CODEWITHSUNDEEP
excelvbaadd-insolver
Tiffany
Tiffany

Reputation: 13

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

Upvotes: 0

Views: 478

Answers (1)

Shai Rado
Shai Rado

Reputation: 33672

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)

Upvotes: 1

Related Questions