Not always. But it doesn't hurt when you do and will save debugging time when
you actually need it.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rTOCtyLst As Range
With Worksheets("sheet2")
Set rTOCtyLst = Range(.Cells(1, 1), .Cells(1, 2))
End With
End Sub

Try putting this code behind Sheet1's module.

The version without the dot (range()) will fail. When you add the dot
(.range(), it'll work ok.

Unqualified ranges in that worksheet module will refer to the worksheet that
owns the code.

In xl2003's help, it says:

When used without an object qualifier, this property is a shortcut for
ActiveSheet.Range (it returns a range from the active sheet; if the active sheet
isn’t a worksheet, the property fails).

But that doesn't look true to me.

I think that excel treats this unqualified Range as application.range.

All these examples have sheet1 the activesheet:

Because this in a general module works fine:

With Worksheets("sheet2")
Set rTOCtyLst = Range(.Cells(1, 1), .Cells(1, 2))
End With

But this fails:

With Worksheets("sheet2")
Set rTOCtyLst = activesheet.Range(.Cells(1, 1), .Cells(1, 2))
End With

But this works ok:

With Worksheets("sheet2")
Set rTOCtyLst = application.Range(.Cells(1, 1), .Cells(1, 2))
End With

=======
And for all the extra time it takes to type that dot, I think it's money well
spent!


Peter T wrote:

Hi Bob,

Is it strictly necessary to qualify the Range with a dot?

Sub test()
Dim ws As Worksheet
Dim r1 As Range, r2 As Range
Set ws = Worksheets("Sheet3")

Worksheets("Sheet1").Activate

With ws
Set r1 = Range(.Cells(1, 1), .Cells(2, 2))
Set r2 = .Range(.Cells(1, 1), .Cells(2, 2))
End With

MsgBox r1.Parent.Name & vbCr & _
r2.Parent.Name

End Sub

Regards,
Peter T

"Bob Phillips" wrote in message
...
The range should be dot qualified also

with activesheet
Set rTOCtyLst = .Range(.Cells(1, 1), .Cells(1, iEndRow))
end with

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter T" <peter_t@discussions wrote in message
...
I've only glanced at your code but try

with activesheet
Set rTOCtyLst = Range(.Cells(1, 1), .Cells(1, iEndRow))
end with

this qualifies the dot that precedes your Cells to the sheet.
Alternatively
just remove those dots if, and only if you are working with the active
sheet.

It would be a good idea to qualify your variables
Dim strOrig as string, strOutcomes As String
Dim rCell as range, rTOCtyLst As Range
Dim iOrigCityNo as long, iEndRow As Long ' not Integer with rows

Regards,
Peter T

"davegb" wrote in message
oups.com...
Still working on this code:

Sub CtyMatch()
Dim strOrig, strOutcomes As String
Dim rCell, rTOCtyLst As Range
Dim iOrigCityNo, iEndRow As Integer

strOrig = ActiveSheet.Range("A2")
iOrigCityNo = Left(strOrig, 2)
iEndRow = ActiveSheet.Range.Cells(Rows.Count, "B").End(xlUp).Row
Set rTOCtyLst = Range(.Cells(1, 1), .Cells(1, iEndRow))

But am getting an "invalid or unqualified reference" error on the last
line, .Cells being highlighted. I copied this line from another thread
here so I'm stumped as to why it doesn't recognize ".Cells" here but
does elsewhere in a nearly identical situation.
Any suggestions?
Thanks.






--

Dave Peterson