Goal Seek-Macro keeps crashing/running slow

27 Views Asked by At

This macro is used make a cell 10%(IRR),by goal seeking a certain cell to reach that point. For some reason the code I have is running super slow/crashing.

Is there a way to make this code more efficient. Code is below.

Sub GoalSeekTo10Percent()
    ' Define variables
    Dim targetCell As Range
    Dim setCell As Range
    Dim goalIRR As Double
    
    ' Set target cell (cell you want to set to 10%)
    Set targetCell = Range("A1") ' Change A1 to your target cell
    
    ' Set set cell (cell you want to change to achieve 10%)
    Set setCell = Range("B1") ' Change B1 to your set cell
    
    ' Set the desired IRR (10%)
    goalIRR = 0.10
    
    ' Run Goal Seek
    GoalSeekIRR targetCell, setCell, goalIRR
End Sub

Sub GoalSeekIRR(targetCell As Range, setCell As Range, goalIRR As Double)
    ' Save the original formula
    Dim originalFormula As String
    originalFormula = setCell.Formula
    
    ' Loop to find the value that achieves the goal IRR
    Do While Abs(targetCell.Value - goalIRR) > 0.0001
        ' Change set cell value and recalculate IRR
        setCell.Value = setCell.Value + 0.01 ' You can adjust the step size
        
        ' Recalculate IRR
        Application.Calculate
    Loop
    
    ' Display a message with the result
    MsgBox "Goal Seek Complete. Set cell value adjusted to achieve the goal IRR."
    
    ' Restore the original formula
    setCell.Formula = originalFormula
End Sub
0

There are 0 best solutions below