Solver is not changing values in target cell using through VBA

Hello to all VBA Wizards!!!

I am struggling with a problem and it is giving me very hard time.

I am using Solver function in a worksheet using VBA.

Initially Solver was being used manually in the Template, however now I want to automate this. I used recorded macro to Maximize and Minimize the Correlation function output.

It worked 3-4 time initially but stopped working at once after it and I have tried everything by deleting/setting the ranges manually but no results.

I Googled it and used Solver.Solver2.Auto_Open but nothing happened.

The VBA code runs, not giving any error but the values are not changing in Target Cell.

Kindly provide the solution if there is any as this task has to be completed by tomorrow max.

Appreciate you help!!

Regards,
Brajpal Shishodia

Thanks for the reply.

Please find the codes below


Sub Max_solver()

' Set up the parameters for the model.
' Determine the maximum value for correlation value in cell O12
' by changing the number of units to build in cells w15:W22.

' Clear any previous Solver settings.
SolverReset

SOLVER.Solver2.Auto_open

'for maximization maxminval =1
'for minmization maxminval = 2
'for meeting speacial value of 75 maxminval =3 and valueof = 75
SolverOk SetCell:=Range("$n$12"), MaxMinVal:=1, _
ByChange:=Range("$w15:$w$41")


'LESS THAN CONSTRAINT
SolverAdd CellRef:=Range("$C$10"), Relation:=1, _
FormulaText:="0.8"

'MORE THAN CONSTRAINT
SolverAdd CellRef:=Range("$C$10"), Relation:=3, _
FormulaText:="0.001"

' Add the constraint for the model. The only constraint is that the
' number of parts used does not exceed the parts on hand--
' $w$15:$w$22 <= $x15:$x$22 and $w$15:$w$22 >= $v15:$v$22
'LESS THAN CONSTRAINT
SolverAdd CellRef:=Range("$w15:$w$41"), Relation:=1, _
FormulaText:="$x$15:$x$41"

'MORE THAN CONSTRAINT
SolverAdd CellRef:=Range("$w15:$w$41"), Relation:=3, _
FormulaText:="$v$15:$v$41"

SolverOk SetCell:="$N$12", MaxMinVal:=1, ValueOf:="0", ByChange:="$W$15:$W$41"

' Do not display the Solver Results dialog box.
SolverSolve UserFinish:=True

' Finish and keep the final results.
SolverFinish KeepFinal:=1

End Sub

Sub Min_solver()

' Set up the parameters for the model.
' Determine the maximum value for correlation value in cell O12
' by changing the number of units to build in cells w15:W22.

' Clear any previous Solver settings.
SolverReset

SOLVER.Solver2.Auto_open

SolverOk SetCell:=Range("$n$12"), MaxMinVal:=2, _
ByChange:=Range("$w15:$w$41")


'LESS THAN CONSTRAINT
SolverAdd CellRef:=Range("$C$10"), Relation:=1, _
FormulaText:="0.8"

'MORE THAN CONSTRAINT
SolverAdd CellRef:=Range("$C$10"), Relation:=3, _
FormulaText:="0.001"


' Add the constraint for the model. The only constraint is that the
' number of parts used does not exceed the parts on hand--
' $w$15:$w$22 <= $x15:$x$22 and $w$15:$w$22 >= $v15:$v$22
SolverAdd CellRef:=Range("$w15:$w$41"), Relation:=1, _
FormulaText:="$x$15:$x$41"

SolverAdd CellRef:=Range("$w15:$w$41"), Relation:=3, _
FormulaText:="$v$15:$v$41"


SolverOk SetCell:="$N$12", MaxMinVal:=2, ValueOf:="0", ByChange:="$W$15:$W$41"

' Do not display the Solver Results dialog box.
SolverSolve UserFinish:=True

' Finish and keep the final results.
SolverFinish KeepFinal:=1

End Sub


Sub Solver_Test()

'SolverOk SetCell:="$N$12", MaxMinVal:=2, ValueOf:="0", ByChange:="$W$15:$W$41"
SolverAdd CellRef:="$C$10", Relation:=1, FormulaText:="0.8"
SolverAdd CellRef:="$C$10", Relation:=3, FormulaText:="0.001"
SolverAdd CellRef:="$W$15:$W$41", Relation:=1, FormulaText:="$X$15:$X$41"
SolverAdd CellRef:="$W$15:$W$41", Relation:=3, FormulaText:="$V$15:$V$41"
SolverOk SetCell:="$N$12", MaxMinVal:=1, ValueOf:="0", ByChange:="$W$15:$W$41"
SolverSolve True
'SolverOk SetCell:="$N$12", MaxMinVal:=2, ValueOf:="0", ByChange:="$W$15:$W$41"
'SolverSolve

End Sub

Hi Shg,

Appreciate your help on this.

Yes, actually I modified the existing code that is why it is reflecting like that.

The problem is in cells "W15:W41".

These cells should change accordingly when I run the code for Maximize/Minimize.

For Maximize the values on W15:W41 should change with X15:X41 and for Minimize W15:W41 values should change with V15:V41, but this is not happening.

it is the cause of problem.

Thanks
Brajpal Shishodia

Hello shg,

I spent a lot of time on it and then finally I found the solution of my problem.

The cell reference issue was there. I tracked it and now the Solver is working fine now.

I really appreciate the prompt response you gave on this post.

Best Regards,
Brapal Shishodia