Automatic GoalSeek With Calculated Target.Address

[Jon Mark]

Hi. I'm very new to VBE, and while I've almost got an automatic GoalSeek function worked out, there's one last problem that I can't seem to find a way around (or a thread that covers it).

As it stands, I can manually enter a value in cell B4 of the sheet with my GoalSeek function, and Excel changes B24 to make D23 equal to B4. Here's the code for that:

Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo TheEnd
If Target.Address = "$B$4" Then
Application.EnableEvents = False
Range("D23").GoalSeek Goal:=Range("B4").Value, _
ChangingCell:=Range("B24")
End If
TheEnd:
Application.EnableEvents = True
End Sub

The problem is this: The value in cell B4 isn't normally entered manually, and is the result of a calculation performed in a different sheet, using data entered manually on a third sheet. Unfortunately, the Target.Address function doesn't seem to recognize that the calculated value in B4 on the GoalSeek function sheet has changed when the entered data is modified, so the GoalSeek doesn't happen.

I found another post with a similar question, but the answer (below) seems to refer to a calculation that takes place on the same sheet as the GoalSeek function - not the case for me. Also, the calculation made in my workbook is the result of 30 or more numbers entered manually - do I really have to list them all?

Where a1 and d3 are precedents causing c2's calculated value to change, replace
If Target.Address = "$C$2" Then
with
"If Not Intersect(Target, [a1,c2,d3]) Is Nothing Then"

Any ideas on how I can solve this? Thanks for any help!

 

[Jon Mark]

Never mind on this question; I figured it out. For anyone else with a similar question, here's what I ended up with:

Code:
Private Sub Worksheet_Calculate()
On Error GoTo TheEnd
Dim OldVal As Variant
If Range("$B$4").Value <> OldVal Then
Application.EnableEvents = False
OldVal = Range("B4").Value
Range("D23").GoalSeek Goal:=Range("B4").Value, _
ChangingCell:=Range("B24")
End If
TheEnd:
Application.EnableEvents = True
End Sub