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