VBA Goal Seek With If Statement (Want Result of Goal Seek to be a Minimum of 0")

[rwilliams09]

I have the following VBA code, but I want to limit O22 at 0 because this is a breakeven analysis with multiple revenue-drivers. The idea is that if O22 is 0, then this is sufficient information. Those using the spreadsheet do not need to know that O22 can be "(999,999,999)" and still breakeven... if that makes sense.

How would I set the parameter at a minimum of 0? Also, would like to set it up in a way that makes it run fast so that it does not have to go through all of the iterations and then settle on 0.

Private Sub WellOilBreakeven_Click()
'
' GoalSeek Macro
'


'
Application.CutCopyMode = False
Application.CutCopyMode = False
Application.CutCopyMode = False
Range("D9").GoalSeek Goal:=0, ChangingCell:=Range("O22")
End Sub


Thank you so much in advance!

 

[JackDanIce]

As a guess try:

Private Sub WellOilBreakeven_Click()
  With Range("D9")
  .GoalSeek Goal:=0, ChangingCell:=Range("O22")
  .Value = Application.Max(0, .Value)
  End With
End Sub

 

[rwilliams09]

What about making it so the macro will only run if a cell (in this case M9) has the number "2" in it? Otherwise, it will not run....

Is that possible?

 

[rwilliams09]

Anyone have any idea?

 

[JackDanIce]

Private Sub WellOilBreakeven_Click()
    If Range("M9").Value = 2 Then
        Application.ScreenUpdating = False
        With Range("D9")
            .GoalSeek Goal:=0, ChangingCell:=Range("O22")
            .Value = Application.Max(0, .Value)
        End With
        Application.ScreenUpdating = True
    End If
End Sub

 

[rwilliams09]

Thanks! This works for the IF M9=2 function - but it looks like the Max (0, .Value) does not work properly...

Is there any other way to ensure the result cannot be below 0?

 

[JackDanIce]

Without the file and based on your description, I'm not sure why the Application.Max(0, .Value) part is not returning a minimum value of 0 if not the goal seek.

 

[rwilliams09]

Hi Jack, I think the problem with the formula is that it makes D9 have a minimum value of 0 and I am looking for O22 to have a minimum value of 0. Does that make sense?

 

[JackDanIce]

What value does O22 have if not 0?

 

[rwilliams09]

The problem I have is that O22 can go as low as like (1,000,000,000) and that takes forever. I just want O22 to have a minimum of 0 or higher.