I am facing a problem with a button I have. I wanna create a button that performs a goal seek analysis when clicking it. The goal seek function is a simple reversed DCF calculation where it should find the implied growth rate for a current stock price.
Private Sub CommandButton1_Click()
Dim Price As String, ImpliedPrice As String
Set ws = Sheet2
Set ws1 = Sheet6
ws.Select
Price = Range("C11")
ws1.Select
ws1.Range("G39").Select
ActiveCell.Value = Price
ws1.Select
Range("G38").GoalSeek Goal:=Price, ChangingCell:=("G8")
End Sub
The variables are the following: Sheet2 is the place where I get the current price from and Sheet6 is where the DCF model is and the goal seek should be performed.
G38 = A reference to the Implied share price, in the referenced cell I simple divide Equity value by outstanding shares to also be able to use the DCF in the normal way.
G8 = the revenue growth rate assumption
When performing the code the 1004 run time error appears but I am not exactly sure why? It would be great if someone could help me figure out why there is this runtime error. Thanks already
if you set worksheet variables for what u re selecting sheets?
try below.
Private Sub CommandButton1_Click()
Dim Price As String, ImpliedPrice As String
Set ws = thisworkbook.worksheets(Sheet2.name)
Set ws1 = thisworkbook.worksheets(Sheet6.name)
Price = ws .Range("C11")
ws1.Range("G39") = Price
ws1.Range("G38").GoalSeek Goal:=Price, ChangingCell:=ws1.range("G8")
End Sub