HELP! VBA Macro Deleting Formula From Cells

[rwilliams09]

I am trying to run a breakeven analysis using a Goal Seek. The Goal Seek looks at the NPV, and should put in a new price to change the NPV to 0. However, I would like for the NPV cell to retain the formula rather than change to the hard-coded number 0 every time I run it.

Is there something that can fix this? My code is the following:

Private Sub CommandButton2_Click()If Range("Q9").Value = 2 Then
        Application.ScreenUpdating = False
        With Range("Q11")
            .GoalSeek Goal:=0, ChangingCell:=Range("P22")
            .Value = Application.Max(0, .Value)
        End With
        Application.ScreenUpdating = True
    End If
End Sub

 

[Rick Rothstein]

I am trying to run a breakeven analysis using a Goal Seek. The Goal Seek looks at the NPV, and should put in a new price to change the NPV to 0. However, I would like for the NPV cell to retain the formula rather than change to the hard-coded number 0 every time I run it.

Is there something that can fix this? My code is the following:

Private Sub CommandButton2_Click()If Range("Q9").Value = 2 Then
        Application.ScreenUpdating = False
        With Range("Q11")
            .GoalSeek Goal:=0, ChangingCell:=Range("P22")
            [B][COLOR="#FF0000"].Value = Application.Max(0, .Value)[/COLOR][/B]
        End With
        Application.ScreenUpdating = True
    End If
End Sub

[untested] It looks to me like the line of code I highlighted in red is what is removing the formula... you can try deleting that line of code and see whether the code still works correctly for you.

 

[rwilliams09]

Worked! Thanks!

 

[rwilliams09]

Now, I'm just wondering if there is a way to make the Goal Seek stop its "seek" once it reaches 0 basically to have 0 as a minimum.

I know that I can have a cell with in IF statement that says that when the goalseek answer is <0, just to put 0. However, the run time is still extremely long. Is there a way to make this not happen?

 

[rwilliams09]

Basically, I want P22 to have a minimum value of 0.

 

[Rick Rothstein]

Basically, I want P22 to have a minimum value of 0.

Unfortunately, I have never used GoalSeek before (I was a Civil Engineer/Programmer during my working career and know pretty much nothing about the financial functions you are using), so I cannot help you with your new question. Based on the description for it, it would have seemed to me that setting the goal at 0 would be all that was necessary.