run-time error '1004': Method 'Intersect' of object '_Global' failed

[cmpgeek]

I have some VBA code that someone helped me with years ago that works great 99% of the time. For some reason, When I try to delete one cell from all the spreadsheets within the workbook at once - or do a replace all on a highlighted group of cells (again - on all spreadsheets at once) I get the run-time error. (I can do the changes one sheet at a time, but there are close to 80 sheets so it is really more efficient if I can avoid that).

I am now using Excel 2010. This was written when I was using Excel 2000. (I don't know if that will matter or not).
I am using Microsoft Visual Basics for Applications 7.0

Option Explicit
Sub MyWsChange(ByVal Target As Range)
     
    If Target.Cells.Count > 1 Then Exit Sub
     
    If Intersect(Target, Range("G:AC")) Is Nothing Then Exit Sub 
     
On Error GoTo errHandler:
     
    With Target
        If IsNumeric(.Value) Then
            Application.EnableEvents = False
            Select Case .Value
            Case 0
                .NumberFormat = "[h]:mm"
            Case 1 To 99
                .Value = TimeSerial(0, .Value, 0)
                .NumberFormat = "[h]:mm"
            Case 100 To 9999
                .Value = TimeSerial(Int(.Value / 100), .Value Mod 100, 0)
                .NumberFormat = "[h]:mm"
            Case Else
            End Select
        End If
    End With
errHandler:
    Application.EnableEvents = True
End Sub

When I hit the button to Debug the code this is where the problem is highlighted:
[highlight] If Intersect(Target, Range("G:AC")) Is Nothing Then [/highlight] Exit Sub


What little I know about VBA is from a "Teach Yourself" book and from examples I have found on similiar forums... I haven't really had the chance to break this code down line by line to be able to understand it, so please forgive me if this is something simple to yall...

Thanks so much...

 

[Jerry Sullivan]

Hi cmpgeek,

I'm assuming that you have this MyWsChange procedure in a Standard Code Module and it's being called by either
Worksheet_Change event code in each sheet or a Workbook_SheetChange procedure in the ThisWorkbook Module.

Typically your problem line of code works by checking to see if there is one cell changed in the range G:AC of the ActiveSheet

If Intersect(Target, Range("G:AC")) Is Nothing Then Exit Sub

The Sheet isn't referenced explicitly in this statement, so VBA interprets this as:

If Intersect(Target, ActiveSheet.Range("G:AC")) Is Nothing Then Exit Sub

The problem is that when you select a group of sheets and change 1 cell as each call is made to the MyWsChange, it tries to find an Intersection of the (Group's) ActiveSheet.Range("G:AC")) and the Target Cell from each Sheet within the Group of Sheets.

Interestingly when you try to test an Intersect with Ranges from two different sheets, Excel doesn't just return Nothing - it throws an error which you can see with this quick test...

Sub TestIntersectionFromTwoSheets()
    
'---Returns "No Intersect"
MsgBox IIf(Intersect(Sheets(1).Range("A1"), _
    Sheets(1).Range("G:AC")) Is Nothing, _
        "No Intersect", "Intersects")

'---Returns "Intersects"
MsgBox IIf(Intersect(Sheets(1).Range("G1"), _
    Sheets(1).Range("G:AC")) Is Nothing, _
        "No Intersect", "Intersects")

'---Throws "run-time error '1004': Method 'Intersect' of object '_Global' failed"
MsgBox IIf(Intersect(Sheets(1).Range("G1"), _
    Sheets(2).Range("G:AC")) Is Nothing, _
        "No Intersect", "Intersects")

End Sub

One way to fix that would be to reference each Target's Sheet using the .Parent Property.

If Intersect(Target, Target[B][COLOR="#0000CD"].Parent[/COLOR][/B].Range("G:AC")) Is Nothing Then Exit Sub