Automatic GoalSeek With Calculated Target.Address

Jon Mark

New Member
Joined
Mar 16, 2011
Messages
3
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!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Jon Mark

New Member
Joined
Mar 16, 2011
Messages
3
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,652
Messages
5,838,591
Members
430,557
Latest member
MK15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top