'Intersect of object' - Global failed

[Biz]

Dear All

I am trying to Intersect method but it fails gives run-time error '1004'.
Method 'Intersect of object' - Global failed.

Ranged names are different worksheet Sheets(B) and Activesheet Sheets ("A") . Not sure why it is not working.

Set tAG = Sheets("B").Range("tAGt")
Set tAT = Sheets("B").Range("tATt")
'AG Changes
    If Not Intersect(Range("E12"), tAG) Is Nothing Then
        Range("E14").Value = ""
        Range("E16").Value = ""
    'AT Changes
    ElseIf Not Intersect(Range("E14"), tAT) Is Nothing Then
        Range("E16").Value = ""
    End If

Your help would be greatly appreciated

Biz

 

[RoryA]

If the ranges are on different sheets, they can't intersect, so I'm not sure what you are trying to do.

 

[Biz]

Hope below statements help.

If someone changes Sheets("A").Range("E12") Then blank out
Sheets("A").Range("E14").Value = ""
Sheets("A"). Range("E16").Value = ""

ElseIf Sheets("A").Range("E14")Then
Range("E16").Value = ""
End If

Biz

 

[RoryA]

What does that have to do with Intersect?

 

[RoryA]

Is this code in a Worksheet_Change event?

 

[Biz]

Yes it is under Worksheet_Change event.
Still not sure how to make it work.

Biz

 

[Biz]

Hi Rorya,

I finally code working. Back to basics.

[COLOR=#1f497d][SIZE=3][FONT=Calibri]Private Sub Worksheet_Change(ByVal Target As Range)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT][/SIZE][/COLOR]
[COLOR=#1f497d]<o:p>[FONT=Calibri][SIZE=3] [/SIZE][/FONT]</o:p>[/COLOR]
[COLOR=#1f497d][SIZE=3][FONT=Calibri]'Changes Parent<o:p></o:p>[/FONT][/SIZE][/COLOR]
[COLOR=#1f497d][SIZE=3][FONT=Calibri]If Target.Address = "$E$12" Then<o:p></o:p>[/FONT][/SIZE][/COLOR]
[COLOR=#1f497d][SIZE=3][FONT=Calibri]    ActiveSheet.Range("E14").Value = ""<o:p></o:p>[/FONT][/SIZE][/COLOR]
[COLOR=#1f497d][SIZE=3][FONT=Calibri]    Range("E16").Value = ""<o:p></o:p>[/FONT][/SIZE][/COLOR]
[COLOR=#1f497d][SIZE=3][FONT=Calibri]End If<o:p></o:p>[/FONT][/SIZE][/COLOR]
[COLOR=#1f497d]<o:p>[FONT=Calibri][SIZE=3] [/SIZE][/FONT]</o:p>[/COLOR]
[COLOR=#1f497d][SIZE=3][FONT=Calibri]'Changes Child<o:p></o:p>[/FONT][/SIZE][/COLOR]
[COLOR=#1f497d][SIZE=3][FONT=Calibri]If Target.Address = "$E$14" Then<o:p></o:p>[/FONT][/SIZE][/COLOR]
[COLOR=#1f497d][SIZE=3][FONT=Calibri]    Range("E16").Value = ""<o:p></o:p>[/FONT][/SIZE][/COLOR]
[COLOR=#1f497d][SIZE=3][FONT=Calibri]End If<o:p></o:p>[/FONT][/SIZE][/COLOR]
[COLOR=#1f497d]<o:p>[FONT=Calibri][SIZE=3] [/SIZE][/FONT]</o:p>[/COLOR]
[COLOR=#1f497d][SIZE=3][FONT=Calibri]Application.EnableEvents = True<o:p></o:p>[/FONT][/SIZE][/COLOR]
[COLOR=#1f497d][SIZE=3][FONT=Calibri]End Sub<o:p></o:p>[/FONT][/SIZE][/COLOR]

Biz

 

[RoryA]

I would amend that to:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.EnableEvents = False
'Changes Parent
If Not Intersect(Target, Range("E12")) is Nothing Then
    Range("E14").Value = ""
    Range("E16").Value = ""
End If

'Changes Child
If Not Intersect(Target, Range("E14")) Is Nothing Then
    Range("E16").Value = ""
End If

Application.EnableEvents = True
End Sub

 

[Biz]

Hi Rorya,

Thanks your code works very well.

I am using code below. Would it be faster or any faults in my code?

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
 
    Select Case True
    Case Target.Address = "$E$12" 'Changes Parent
        Debug.Print Target.Address
        Range("E14").Value = ""
        Range("E16").Value = ""
    Case Target.Address = "$E$14" 'Changes Child
        Debug.Print Target.Address
        Range("E16").Value = ""
    Case Else
        Application.EnableEvents = True
        Exit Sub
    End Select
 
Application.EnableEvents = True
End Sub

Biz

 

[RoryA]

Your code wouldn't do anything if for example you selected E12:E14 and pressed delete. You should also always have an error handler (even if just an On Error Resume Next) if you disable events in your code.