Problem with Goalseek Macro

[Ken G.]

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.

 

[Jacob Skaria]

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

 

[Ken G]

Thanks Jacob.

This line ..
If Not Application.Intersect(Target, Range("D20") Is Nothing Then
.... is giving a syntax error.

 

[Ken G]

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")

 

[Ken G]

The problem appeared to be that D56 contained a formula. I changed the macro
to do the calculation in D56 so that goalseek sees it is a value and it now
works.

 

[Jacob Skaria]

Ken , good to hear it is working..

1. I missed thhe closing braces ...
2. The below line will make sure that the target is D20...
If Not Application.Intersect(Target, Range("D20")) Is Nothing Then


If this post helps click Yes