Public Sub SolveFive(Optional ShowAlert As Boolean = True)
'Purpose : Runs Solver Loops through the '5YrChoice_MVoptions' Tab
'Author : Jimmy Briggs <jimmy.briggs@pwc.com>
'Description: Automate Workflow for RLUS Client
'Date : 2022-02-04
Application.ScreenUpdating = False
Application.DisplayStatusBar = True
Application.Cursor = xlWait
On Error GoTo HandleError
Dim changeCells As Range
Dim Result As Integer
Dim i As Integer
Dim StartTime As Double
Dim SecondsElapsed As Double
StartTime = Timer
Application.StatusBar = "Starting Macro for 5YrChoice_MVoptions, please be patient..."
Sheets("5YrChoice_MVoptions").Select
For i = 3 To 62 Step 1
Application.StatusBar = "Running Solver on iteration " & i & " out of 62."
Set changeCells = ActiveSheet.Range(Range(Cells(i, 28).Address, Cells(i, 29).Address).Address)
SolverReset
SolverOptions precision:=0.000000001
SolverOK SetCell:=Cells(i, 35).Address, MaxMinVal:=2, byChange:=changeCells.Address
SolverAdd CellRef:=Cells(i, 36).Address, Relation:=2, FormulaText:=0
SolverAdd CellRef:=changeCells.Address, Relation:=3, FormulaText:=0.0000000001
Result = SolverSolve(True)
If Result <= 3 Then
SolverFinish KeepFinal:=1
Else
Beep
MsgBox "Solver was unable to find a solution.", vbExclamation, "SOLUTION NOT FOUND"
SolverFinish KeepFinal:=2
GoTo Skip
End If
Skip:
SolverFinish KeepFinal:=2
Next i
Sheets("Comparison").Select
SecondsElapsed = Round(Timer - StartTime, 2)
Range("Latest_Execution_Time_5").Value = SecondsElapsed
If ShowAlert = True Then
MsgBox "Successfully ran code in " & SecondsElapsed & " seconds", vbInformation
End If
Application.StatusBar = "Done running Solver for sheet 5YrChoice_MVoptions."
Application.OnTime Now + TimeValue("00:00:07"), "clearStatusBar"
Application.ScreenUpdating = True
HandleExit:
Application.Cursor = xlDefault
Exit Sub
HandleError:
MsgBox Err.Description
Resume HandleExit
End Sub
