I am trying to use VBA to solve for a variable to get three different returns: 10%, 16%, Breakeven. I have to use solver to avoid circular references, but I have 5 different scenarios to solve for all three criteria on. So, I thought automating the solver process would speed things up tremendously.

This is my code:

Sub EntSolver()

If Range(“B8”).Value = “0.16” Then
Solverok SetCell:=”E44″, MaxMinVal:=3, ValueOf:=”.16″, ByChange:=”E9″
SolverSolve (True)

ElseIf Range("B8").Value = "0.10" Then

Solverok SetCell:="E44", MaxMinVal:=3, ValueOf:=".1", ByChange:="E9"
SolverSolve (True)

ElseIf Range("B8").Value = "Net Income B/E" Then

Solverok SetCell:="E45", MaxMinVal:=3, ValueOf:="0", ByChange:="E9"
SolverSolve (True)

End If

'find a solution by solving the problem

If Range(“B8”).Value = “0.16” Then
Solverok SetCell:=”I44″, MaxMinVal:=3, ValueOf:=”.16″, ByChange:=”I9″
SolverSolve (True)

ElseIf Range("B8").Value = "0.10" Then

Solverok SetCell:="I44", MaxMinVal:=3, ValueOf:=".10", ByChange:="I9"
SolverSolve (True)

ElseIf Range("B8").Value = "Net Income B/E" Then

Solverok SetCell:="I45", MaxMinVal:=3, ValueOf:="0", ByChange:="I9"
SolverSolve (True)

End If

'find a solution by solving the problem

If Range(“B8”).Value = “0.16” Then
Solverok SetCell:=”M44″, MaxMinVal:=3, ValueOf:=”.16″, ByChange:=”M9″
SolverSolve (True)

ElseIf Range("B8").Value = "0.10" Then

Solverok SetCell:="M44", MaxMinVal:=3, ValueOf:=".10", ByChange:="M9"
SolverSolve (True)

ElseIf Range("B8").Value = "Net Income B/E" Then

Solverok SetCell:="M45", MaxMinVal:=3, ValueOf:="0", ByChange:="M9"
SolverSolve (True)

End If

'find a solution by solving the problem

If Range(“B8”).Value = “0.16” Then
Solverok SetCell:=”Q44″, MaxMinVal:=3, ValueOf:=”.16″, ByChange:=”Q9″
SolverSolve (True)

ElseIf Range("B8").Value = "0.10" Then

Solverok SetCell:="Q44", MaxMinVal:=3, ValueOf:=".10", ByChange:="Q9"
SolverSolve (True)

ElseIf Range("B8").Value = "Net Income B/E" Then

Solverok SetCell:="Q45", MaxMinVal:=3, ValueOf:="0", ByChange:="Q9"
SolverSolve (True)

End If

If Range(“B8”).Value = “0.16” Then
Solverok SetCell:=”U44″, MaxMinVal:=3, ValueOf:=”.16″, ByChange:=”U9″
SolverSolve (True)

ElseIf Range("B8").Value = "0.10" Then

Solverok SetCell:="U44", MaxMinVal:=3, ValueOf:=".10", ByChange:="U9"
SolverSolve (True)

ElseIf Range("B8").Value = "Net Income B/E" Then

Solverok SetCell:="U45", MaxMinVal:=3, ValueOf:="0", ByChange:="U9"
SolverSolve (True)

End If

'find a solution by solving the problem

End Sub

Cell B8 is a data validation dropdown menu that has the 10, 16, and Breakeven % options. The user should be able to choose the return, then run the code. It works for the 16% and Breakeven options, but for some reason, it will not work for the 10% option. I manually used solver to find the 10% variable, and it works, so I have no clue why it won’t work. Please help if you have any idea about what is going on. Thanks so much.