Range being set on wrong sheet, but why?

[cbrf23]

I'm a little confused about why i'm getting an error with this code. Run-time error '1004': Method 'Intersect' of object '_Global' failed.

This code is written in the worksheet module for sheet "INPUT (2)"

Sub Worksheet_Change(ByVal Target As Range)
'''''''''''''''''''''''''
Dim 'delcarations'<ALL declarations my declarations<ALL><ALL here up declarations my> as <CORRECT types types<APPORPRIATE>'proper type'
'''''''''''''''''''''''''
Application.EnableEvents = False
'''''''''''''''''''''''''
strInBk = ThisWorkbook.Name
strOutBk = ThisWorkbook.Name
strInSht = "INPUT (2)"
strOutSht = "test"
lngStart = Workbooks(strOutBk).Sheets(strOutSht).Range("A:A").Find(What:="ITEM", LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Row
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Set EValuator = New ClaEvaluator
        If Target.Address = "$H$2" Then
            strCase1 = "unitChange"
        ElseIf Selection.Rows.Count = 1 And Selection.Columns.Count = 1 Then
            strCase1 = "singleCell"
        ElseIf Target.Address = Target.EntireRow.Address Then
            strCase1 = "wholeRow"
        ElseIf Target.Address = Target.EntireColumn.Address Then
            strCase1 = "wholeCol"
        ElseIf Selection.Rows.Count > 1 Or Selection.Columns.Count > 1 Then
            strCase1 = "multiCell"
        End If
        '''''''''''''''''''''''''
        Select Case strCase1
        '''''''''''''''''''''''''
        Case "unitChange"
            With EValuator
                .Range = Intersect(funTrueUsedRange, Range("3:65536"))
                .Multi = True
            End With
            Workbooks(strOutBk).Sheets(strOutSht).Activate
FAIL--->    Set rngClear = Intersect(funTrueUsedRange, Range(lngStart + 1 & ":65536"))
            rngClear.Clear
        '''''''''''''''''''''''''
            ''next case

If I step through each line with F8 and stop just before the failure, I can type in the immediate window

?funTrueUsedRange.Parent.Name

And I get the correct sheet -"test"- so I know that bit of code is working proper.
And if I then type

?Range(lngStart + 1 & ":65536").Parent.Name

I get the wrong sheet -"INPUT (2)"-but I dont understand why.

If I'm using

Workbooks(strOutBk).Sheets(strOutSht).Activate

to set sheet "test" as the activesheet, shouldnt any ranges without a sheet specified refer to the active sheet?
I've found the code below works, but I dont understand why I have to specify activesheet with that range, after setting the active sheet explicitly?

        Case "unitChange"
            With EValuator
                .Range = Intersect(funTrueUsedRange, Range("3:65536"))
                .Multi = True
            End With
            Workbooks(strOutBk).Sheets(strOutSht).Activate
            Set rngClear = Intersect(funTrueUsedRange, ActiveSheet.Range(lngStart + 1 & ":65536"))
            rngClear.Clear

 

[Jonmo1]

When code is written in a Sheet's Module:
1. Any UNqualified ranges (meaning the sheet is not specified), then it refers to the Sheet the code is written in.
Regardless of which sheet has been activated/selected in previous code.

2. You MUST qualify the sheet you intend to refer to if it is NOT the same as the sheet the code is written in.

Try
Set rngClear = Intersect(funTrueUsedRange, Workbooks(strOutBk).Sheets(strOutSht).Range(lngStart + 1 & ":65536"))

 

[Ruddles]

Generally speaking I've found it easier to bite the bullet and fully qualify all references to worksheet ranges. It might take slightly longer to code but when you're trying to understand your programming (maybe some time) later, there's no question as to which worksheet you intended to refer to.

Also if the active worksheet does change for some reason - maybe you have to amend the code for some reason - you know you don't need to spend any time reading through the rest of your code looking for places where it might fall over as a result.

 

[cbrf23]

jonmo1, that makes a lot of sense. Thank you for clarifying that for me.
Wrote it like so:

 Set rngClear = Intersect(Workbooks(strOutBk).Sheets(strOutSht).Range(lngStart + 1 & ":65536"), _
Workbooks(strOutBk).Sheets(strOutSht).Range(funTrueUsedRange.Address))

Ruddles, I like to use with statements when qualifying worksheets ranges. The indentation pattern I use when writing code makes it real easy to find and change later.

Some Code goes Up Here
Some Code goes Up Here
Some Code goes Up Here
If Some Code Then Some other Code
Some Code goes Up Here
With Workbook(xxx).Worksheets(xxx) 
.Range [or whatever you want]
End With
Some Code goes Up Here
Some Code goes Up Here

 

[Norie]

Using a With statement properly should assist in fully qualifying references.

 

[cbrf23]

Ended up changing to:

            With Workbooks(strOutBk).Sheets(strOutSht)
                Set rngClear = Intersect(.Range(lngStart + 1 & ":65536"), .Range(funTrueUsedRange.Address))
            End With

But couldn't edit my last post.

Also, I dont know why what I typed in the other post didnt indent properly...

Anyways, thanks for the help guys!