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!
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,425
Messages
5,528,688
Members
409,830
Latest member
KT50

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top