Cell reference to solver add-in

[yugal40]

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

Thanks

[#2]

@yugal40 

You have to specify ranges:

Sub Macro3()
    SolverReset
    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

[#3]

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.

[#4]

@Hans Vogelaar 
Sorry for the multiple post
PFA

[#5]

@yugal40 

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.

[#6]

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

[#7]

@yugal40 

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

[#8]

Why it would be unrealistic?

[#9]

@yugal40 

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.