RLUS - Hedging - SolveFive

PHOTO EMBED

Mon Feb 07 2022 18:08:40 GMT+0000 (Coordinated Universal Time)

Saved by @jimbrig #vba #excel

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
content_copyCOPY