Goal Seek VBA by a Newbie

P

petersing

New Member
Joined
Oct 18, 2003
Messages
7
I have created the following in an effort to automatically goal seek whenever changes are made to the cell C2.

Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Row = 3 And Target.Column = 2 Then
Range("D19").GoalSeek Goal:=Range("C2").Value, _
ChangingCell:=Range("D3")
End If
End Sub

As far as I know, the Macro is running all of the time? When I use goal seek manually it works fine. Assuming the above is running, when I change the value in C2 nothing happens. Does the fact that I have a three sheet workbook make any difference? I am sort of lost. By changing the value in C2 I want Excel to change the value in D19 to match C2 by adjusting D3.

Thanks
Pete

I got one reply that I interpreted thusly:

Application.EnableEvents = False
Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Row = 3 And Target.Column = 2 Then
Range("D19").GoalSeek Goal:=Range("C2").Value, _
ChangingCell:=Range("D3")
End If
End Sub
Application.EnableEvents = True

Doesn't seem to work either. However, I don't really know if other settings in the workbook are correct. I think I need lots of help.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Your first problem is that your NOT testing for a change in C2 with "Target.Row = 3 And Target.Column = 2" ... Your testing for a change in "B3" .

Below I've modified your code so that it is triggered by change in "C2" ... does this help ?



Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo TheEnd
If Target.Address = "$C$2" Then
Application.EnableEvents = False
Range("D19").GoalSeek Goal:=Range("C2").Value, _
ChangingCell:=Range("D3")
End If
TheEnd:
Application.EnableEvents = True
End Sub
 
Upvote 0
I modified the text to what you posted. Still no luck. Thanks for trying. Any other ideas?

Manual Goal Seek still works OK.

Sorry for the mixup in Rows and Columns.

Pete
 
Upvote 0
Let's make sure the code is running by inserting a Pop Up messge . This message should fire everytime C2 is modified.

Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo TheEnd
If Target.Address = "$C$2" Then
MsgBox "I'm running"
Application.EnableEvents = False
Range("D19").GoalSeek Goal:=Range("C2").Value, _
ChangingCell:=Range("D3")
End If
TheEnd:
Application.EnableEvents = True
End Sub

Do you get a popup messge ?? :eek:
 
Upvote 0
No Popup. Remember I am a newbie and am in uncharted waters here. I am using 'tools' 'macro' 'visual basic editor'. I have written the macro and saved it. Then I exit the editor and expect the macro to function. I don't know if it is 'loaded' or what.

Pete
 
Upvote 0
I found out where the macro was residing and then copied it into the sheet module. It is working!! o_O

Thanks for your help.

Now here is another twist. If C2 is a calculated number and not just entered in, can I make it work somehow?
 
Upvote 0
Hello Peter, change the target.address = "$c$2" to:

If Not Intersect(Target, [a1,c2,d3]) Is Nothing Then

Where a1 and d3 are precedents causing c2's calculated value to change.
 
Upvote 0
thanks Nate, It worked. I guess if you get enough people holding your hand you can muddle your way through without knowin nuttin. It is a good way to learn what you need but it takes awhile.

Pete
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question
VBA Code for bulk Goal Seek
Replies
6
Views
236
hellokitty19
H
S
  • Solved
VBA Help Goal Seek with dynamic cell
Replies
5
Views
362
smittymj
S
A
  • Solved
Goal seek VBA with If statement
Replies
3
Views
148
hrayani
H
P
  • Question
Error on Loop
Replies
3
Views
287
Pestomania
P
F
  • Solved
VBA change event applied to one or multiple cells
Replies
3
Views
72
FryGirl
F

Forum statistics

Threads
1,215,268
Messages
6,123,965
Members
449,137
Latest member
yeti1016

Share this page

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
Back
Top