Add a User-selected range to Solver Macro

[samanthalane]

Ok I admit I may be a little over my head trying this but I want to score some brownie points in my internship.

I used the macro recorder to record a macro for using solver as shown below:

Sub BreakEven()
'
' BreakEven Macro
'


SolverOk SetCell:="$G$32", MaxMinVal:=3, ValueOf:="250000", ByChange:= _
"$D$2:$D$31"
SolverSolve
End Sub

Everything will stay the same except that the ValueOf:="250000" I want to be user selected by choosing the cell on another worksheet.

Another thought I had is that the cell containing the "250000" will always be the same cell referenced so I'm guessing I can somehow easily define a variable to plug into the code above so that it would be something like: ValueOf:="cost" and then somewhere I define cost referencing that "250000" cell (which is on another page)

Help please. I'm sure these are silly basic questions.

 

[VoG]

Hello Samantha and welcome to MrExcel.

Try something like this

Sub BreakEven()
'
' BreakEven Macro
'


SolverOk SetCell:="$G$32", MaxMinVal:=3, ValueOf:=Sheets("Sheet1").Range("G4").Value, ByChange:= _
"$D$2:$D$31"
SolverSolve
End Sub

Change the sheet name and cell to suit.