VBA, Macros for Solver

[Yash0071]

Hello,

>>> as You've already noted <<<
>>> use code - tags <<<​

I'm working on Variance optimization from 2 days with VBA support

 (Solver)
Sub TestSolver()
    SolverOk SetCell:="$H$13", MaxMinVal:=1, ByChange:="$G$2:$G$6", Engine:=1, EngineDesc:="GRG Nonlinear"
    ' Add criteria - F3 must be equal to 1
    SolverAdd CellRef:="$G$7", Relation:=2, FormulaText:="1"
    ' Add criteria - H13 must be equal to 18
    SolverAdd CellRef:="$H$13", Relation:=2, FormulaText:="18"

    ' Find a solution by solving the problem
    SolverSolve
End Sub

I have big issues due to this formula. All the cell formulas are vanishing off after running this macro. Plus the final result 18 is not coming but the max result in optimization is outputted. Can anyone help me to protect my cell formulas against solver? Also help on getting 18 result.

Forgot to mention, recorded macros and it made my excel sheet more worse but the macro is working. Im using 365 I believe which is standard right now.

I got a message saying use Code tags, does it mean I have to type CODE: at the beginning please?

Regards

 

[vletm]

Yash0071​

As You've read all links from

New Users - Please Start Here

Important Information for New and Existing Users

chandoo.org

You've found page

Posting a sample workbook

The Chandoo.org Forum now has the facilities to upload files directly into a post. Simply use either the Media icon in the Edit menu or the Upload a File Button next to the Save Changes button Alternatively In the Chandoo.org Blog or here in the Forums you can still upload files using a Free...

chandoo.org

You've found one way - how to use code - tags.

 

[Yash0071]

Im working on my question today and cross referenced query with a different code on MS forums. Some of the problems about formula corruption could be stopped by using this code. But the output is not working because its returning False condition for a constraint.

Sub MaxSolver()
Dim ws As Worksheet
Dim formulaCells As Range   
Set ws = Worksheets("MANUAL")    ws. Activate   
' Lock formula cells   
Set formulaCells = Range("G2:G6")   
formulaCells.Locked = True   
ws. Protect UserInterfaceOnly:=True   
SolverReset    SolverOptions precision:=0.001   
SolverOK setCell:=Range("H16"), _             
maxMinVal:=1, _             
byChange:=formulaCells             
SolverAdd cellRef:=Range("G7"), _             
 relation:=2, _             
 formulaText:=1   
SolverAdd cellRef:=Range("H13"), _             
relation:=1, _             
formulaText:=22 
' Set target value for H13   
SolverSolve userFinish:=False   
SolverSave saveArea:=Range("H20")   
' Unlock formula cells   
formulaCells.Locked = False   
ws. Protect UserInterfaceOnly:=True, _               
AllowFormattingCells:=True
End Sub