I fixed the syntax error but its still giving the same 1004 error message at
the same place saying the reference is invalid...

Range("F66").GoalSeek Goal:=Range("D4").Value, _
ChangingCell:=Range("D56")



"Jacob Skaria" wrote:

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.