Cell reference to solver add-in


Hi Folks,

I am trying to give cell reference to solver add-in in excel VBA window, but i am unable to do so.
Can anyone please help me for the same?

Open for different approaches if this is not possible


8 Replies


You have to specify ranges:

Sub Macro3()
    SolverOk SetCell:=Range("I11"), MaxMinVal:=3, ValueOf:=Range("C3").Value, ByChange:=Range("G9"), _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverAdd CellRef:=Range("G9"), Relation:=3, FormulaText:=0
    SolverSolve UserFinish:=True
End Sub
Hi, Thanks for your reply, I am not sure if i am missing something but your code is not working in my workbook. I have just tried.

@Hans Vogelaar 
Sorry for the multiple post


That is because the total for A and B, without C is 182195, well over the target of 153500. So there is no solution.

If you either increase the target to above 182195, or decrease the percentage for A and/or C so that the total is less than 153500, the code will find a solution.

Thanks for your reply
Its strange that we don't have a solution to this, But no worries thanks for the help


There would be a solution if you allowed the price to be negative. But I assume that that is not realistic.

Why it would be unrealistic?


In most real-world situations, prices cannot be less than zero.

But you can try it for yourself: if you allow the price to be negative, then Solver will find a solution.

See the attached version.