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

 

[Iridium]

Hi there and welcome to the board.

Just for info post your questions in the 'Excel Questions' forum - you should get more replies

HTH

 

[Mudface]

This post will likely be moved, but here's a quick answer. I think your problem is that you're using a change event sub which also changes the worksheet, causing it to be called recursively. Add

Application.EnableEvents = False

as the first line of your sub, change it back to True as the last line.

 

[NateO]

Well, I moved this thread, and now am going to lock it, might as well continue with the more active discussion eh?

http://www.mrexcel.com/board2/viewtopic.php?t=66528

I wonder what I'll be doing to this thread next...