How to Use Solver from VBA

[shailyog]

I am facing a difficulty in using solver from VBA.


I created a code for solving some problem.

While running the same it gave an error: Compile error: Can't Find project or Library.


then I went to Tools>References>Solver and click on the box next to it and checked it.


after clicking "Ok" I encountered another alert message: "Name Conflict with existing Module, Project or Object Library".


what to do, not able to find out what is wrong. Please Help


I have Excel 2010.

 

[NARAYANK991]

Hi Shailyog ,


Can you post your code here , or upload your workbook ?


Narayan

 

[shailyog]

This is my Code

Sub SolverMacro()

'

' SolverMacro Macro

' Macro recorded ****** by **********

'


'

Application.ScreenUpdating = False

If Range("a18").Value = "Total Cost" Then

SolverReset

SolverOk SetCell:="$B$18", MaxMinVal:=2, ValueOf:="0", ByChange:="$B$2:$E$4"

SolverAdd CellRef:="$F$2", Relation:=2, FormulaText:="$G$2"

SolverAdd CellRef:="$F$3", Relation:=2, FormulaText:="$G$3"

SolverAdd CellRef:="$F$4", Relation:=2, FormulaText:="$G$4"

SolverAdd CellRef:="$B$5", Relation:=2, FormulaText:="$B$6"

SolverAdd CellRef:="$C$5", Relation:=2, FormulaText:="$C$6"

SolverAdd CellRef:="$D$5", Relation:=2, FormulaText:="$D$6"

SolverAdd CellRef:="$E$5", Relation:=2, FormulaText:="$E$6"

SolverAdd CellRef:="$B$2:$E$4", Relation:=3, FormulaText:="0"

SolverSolve userfinish:=True


Else


SolverReset

SolverOk SetCell:="$B$18", MaxMinVal:=1, ValueOf:="0", ByChange:="$B$2:$E$4"

SolverAdd CellRef:="$F$2", Relation:=2, FormulaText:="$G$2"

SolverAdd CellRef:="$F$3", Relation:=2, FormulaText:="$G$3"

SolverAdd CellRef:="$F$4", Relation:=2, FormulaText:="$G$4"

SolverAdd CellRef:="$B$5", Relation:=2, FormulaText:="$B$6"

SolverAdd CellRef:="$C$5", Relation:=2, FormulaText:="$C$6"

SolverAdd CellRef:="$D$5", Relation:=2, FormulaText:="$D$6"

SolverAdd CellRef:="$E$5", Relation:=2, FormulaText:="$E$6"

SolverAdd CellRef:="$B$2:$E$4", Relation:=3, FormulaText:="0"


SolverSolve userfinish:=True

Application.ScreenUpdating = True


End If


End Sub

 

[NARAYANK991]

Hi Shailyog ,


When I copy your code and run it , I do not get any error ; the error message suggests that you have other code in your workbook with an identical name ; can you check ?


Can you step through your code by pressing F8 with the cursor placed anywhere within the code ? Each press of the F8 key will execute one program statement. Do you get the error before execution of any statement or does it happen somewhere within the procedure ?


Narayan

 

[shailyog]

Thanks for the help Narayan.


The program used to stop at "Solverreset"


Then i looked into reference library and found the Problem. In reference library there was one reference to "Missing:Solver.xla". I unchecked that and then checked the "Solver" and the code worked.


Thanks again for your time and support in this regard.





Yogesh

 

[NARAYANK991]

Hi Yogesh ,


Great. Congrats.


Narayan