Goal Seek VBA by a Newbie

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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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

Like to get deeper into Goal Seek.

I am trying below exercise

C2 = 200

Have series of formulas in different cells. at the end i have result in f25 as

F25 = 84.76.

Now I have to check if F25 is > 80, if yes i have to change the Value C2 so that i can get 80 in the Cell F25.

If F25 is <= 80 then leave as it.

Please help me out to achieve the above exercise NateO.
 
Upvote 0
I have a further question for a similar problem. I need it to run this code for a row of cells but only in one of my many sheets. How would I do this?
 
Upvote 0
Hi NateO,

What can I do if the precedents are located on a different sheet (where the calculation is actually made), and there are a lot of them?

Thanks,
JM
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

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