Problem with Goalseek Macro
Try this. (untested)..You can disable the events and then enable it once your
code executes....
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Application.Intersect(Target, Range("D20") Is Nothing Then
Application.EnableEvents = False
Range("F66").GoalSeek Goal:=Range("D4").Value, _
ChangingCell:=Range("D61")
If Range("D61").Value < 0 Then
Range("D61").Value = 0
Range("F66").GoalSeek Goal:=Range("D4").Value, _
ChangingCell:=Range("D56")
End If
Application.EnableEvents = True
End If
End Sub
If this post helps click Yes
---------------
Jacob Skaria
"Ken G." wrote:
I've written a macro in Excel 2003 to perform the goalseek function, but
there's a problem. In the code below I want F66 to be equal to D4 by changing
D61. If this causes D61 to go less than zero, then it gets set to zero and
the second goal seek comes into play this time changing cell D56.
I get an error message highlighting the second goal seek code saying error
code 1004, Invalid reference.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Row = 20 And Target.Column = 4 Then
Range("F66").GoalSeek Goal:=Range("D4").Value, _
ChangingCell:=Range("D61")
If Range("D61").Value < 0 Then
Range("D61").Value = 0
Range("F66").GoalSeek Goal:=Range("D4").Value, _
ChangingCell:=Range("D56")
End If
End If
What am I doing wrong?
Its possible that I might need to extend this to set D56 to zero if it goes
under and run goalseek on another cell.
|