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
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter