Help with code for Goal Seek

[LSB M]

Hi, I have the following code, but it doesn't work!


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Row = 15 And Target.Column = 9 Then
Range("M40").GoalSeek Goal:=0, ChangingCell:=Range("M38")
Range("M51").GoalSeek Goal:=0, ChangingCell:=Range("M49")
End If
End Sub

--
Basically I have a drop down bar in cell R15C9, everytime that cell changes, I want Goal Seek to automatically make 2 calculations, one is to change M38 so that M40=0, and the other is to change M49 so that M51=0.

Somehow the code above isn't working for me. Any idea why?

Thanks in advance for any help you can give!

 

[shg]

If you're description is correct, you have M49 and M51 reversed in your code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Address = "$I$15" Then
        Range("M40").GoalSeek Goal:=0, ChangingCell:=Range("M38")
        Range("M49").GoalSeek Goal:=0, ChangingCell:=Range("M51")
    End If
End Sub

 

[LSB M]

oops, it was my description that was off. I edited my original post.

 

[shg]

OK.

What happen when the code runs? Is M40 dependent on M38? Does it work from the UI?

 

[LSB M]

Cell R15C9 has three selections (3 different scenarios which, using the choose function, is fed into the calculations to spit out the P&L).

When I change cell R15C9, values update. With the above code, I had hoped that M38 and M49 would change too but basically nothing happens (the values in M38 and M49 stays the same with each different selections made in cell R15C9).

M40 is dependent on M38; and M51 is dependent on M49.

 

[shg]

Does the goalseek work when you do it from the user interface?

EDIT: BTW, the code works fine for me.

 

[LSB M]

Sorry, I'm a newbie when it comes to macros and VBA. When you say User Interface, what do you mean?

I tried to debug using the "Step Into" function ("F8") but I just get a "beep" when I do that. And when I try to Run the Sub ("F5"), I don't even see the macro listed in the pop up box.

Pretty confusing...

 

[shg]

Data > What-If Analysis > Goal-Seek...

 

[LSB M]

Oh, yes, when doing it manually, everything works fine.

 

[shg]

I don't have a suggestion -- as I said, the code works for me. If you want to post a workbook where you cross-posted, I'll look at it.

(And if you're going to cross-post, please have the courtesy to post a link to the other forums on each forum.)