Goal Seek VBA by a Newbie

[petersing]

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.

 

[NateO]

Indeed. You are welcome.

 

[malickhashim]

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.

 

[ishcabobby]

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?

 

[Jon Mark]

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