Excel Solver in VBA

Question

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

is my code working ?

Shai Rado
– Shai Rado

2016-08-25 13:01:00 +00:00
Commented Aug 25, 2016 at 13:01 — Shai Rado
– Shai Rado, Commented Aug 25, 2016 at 13:01
Yes! Thanks so much!

Tiffany
– Tiffany

2016-08-26 22:32:38 +00:00
Commented Aug 26, 2016 at 22:32 — Tiffany
– Tiffany, Commented Aug 26, 2016 at 22:32

Shai Rado · Accepted Answer · 2016-08-25 03:08:29Z

1

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)

answered Aug 25, 2016 at 3:08

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel Solver in VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related