How to automatically triger Goalseek macro?

[yichuansancun]

I have the following code:

Sub Goalseek()

Range("$N$7").Select
Range("$N$7").Goalseek Goal:=Range("$N$9").Value, ChangingCell:=Range("$N$3")

End Sub

How can I make excel to run this macro automatically every time there is a change in cell N9's value?

Thanks,
Perri

 

[Sthrncali]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$N$9" Then
'Your Macro Here
End If
End Sub

This goes in the Worksheet Code - Right click on the worksheet name and click "View Code" then paste this in.

 

[yichuansancun]

I input the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$N$9" Then
Macro Goalseek
End If
End Sub

it gave me the following error:
Compile Error
Expected Function or Variable

and "Goalseek" is highlighted when the error message appears.

what did I do wrong?

 

[Smitty]

Take out Macro and just use "Goalseek".

Note that it would be a good idea to change the name of the macro, so it's not the same as the method you're invoking.

HTH,

 

[yichuansancun]

Thanks, now I have the following codes:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$N$9" Then
Goalseek123
End If
End Sub

The macro Goalseek123 works fine if I trigger it. But the above code is not working. When there is a change in cell N19, the macro Goalseek 123 is not triggered.



Any thoughts?

Thanks much!

Perri

 

[Smitty]

When there is a change in cell N19, the macro Goalseek 123 is not triggered.

You're telling the code to only look at changes in N9, not N19.

 

[yichuansancun]

Sorry typo

Cell should be N9.

 

[Smitty]

Is it in the proper module?

What happens if you go into the Immediate window and type ?Application.EnableEvents ?

You might also add a MsgBox before Goalseek123 and see if you get anything.

 

[yichuansancun]

when I type MsgBox in front of Goalseek123, it gives the error message "compile error" again

I have the macro Goalseek123:
Sub Goalseek123()

If LCase(Range("education_selection").Value) Like "*mercer data*" Then
Range("education").ClearContents
End If

If LCase(Range("choice").Value) Like "*specified amount*" Then
Range("housing_selection").ClearContents
ElseIf LCase(Range("choice").Value) Like "*mercer data*" Then
Range("housing").ClearContents
End If

Range("$N$7").Select
Range("$N$7").Goalseek Goal:=Range("$N$9").Value, ChangingCell:=Range("$N$3")

End Sub

How can I write a code that will trigger this macro if there is change in value in N9?

Sorry for the back and forth... but I cannot figure it out

Thanks much!

 

[RSXchin]

is N9 a formula? or a static value?