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.

"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.

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.

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.

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.

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

Hi Dave,

Good point about use in worksheet module.

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

My resources can just about stretch to an extra dot, as you say might repay
if ever copying code from a normal module to a worksheet module !!

Regards,
Peter T


"Dave Peterson" wrote in message
...
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

No, it's not essential, until you get in a sheet module:

Private Sub CommandButton1_Click()
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

So for continuity, it would make sense to get in the habit.

--
Regards,
Tom Ogilvy


"Peter T" <peter_t@discussions wrote in message
...
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.

Hi Tom,

I guess Dave's comments and my follow-up were not visible when you posted,
but thanks also.

Dave mentioned that if Range is not qualified, either with a dot to some
sheet or by default to the sheet if in a worksheet module, it defaults to
the Application.

So in a sheet module could use like this:

' in Sheet1 module
Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim r1 As Range, r2 As Range
Set ws = Worksheets("Sheet3")

Worksheets("Sheet1").Activate

With ws
Set r1 = Application.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

This makes sense. In VB6 both Range & Cells always need to be qualified,
even if working on the active sheet (& even if a ref to Excel has been set
in the vb6 project ref's)

Set xlApp = the current Excel instance
Set ws1 = .ActiveSheet ' ref'd back to wb & app

Set rng = xlApp.Range(ws1.Cells(1, 1), ws1.Cells(2, 2))
or
Set rng = ws1.Range(ws1.Cells(1, 1), ws1.Cells(2, 2))

Regards,
Peter T


"Tom Ogilvy" wrote in message
...
No, it's not essential, until you get in a sheet module:

Private Sub CommandButton1_Click()
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

So for continuity, it would make sense to get in the habit.

--
Regards,
Tom Ogilvy


"Peter T" <peter_t@discussions wrote in message
...
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.

Peter T wrote:
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

Why not interger with rows if there's never going to be more than 80 of
them?

"davegb" wrote in message
oups.com...

Peter T wrote:
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

Why not interger with rows if there's never going to be more than 80 of
them?

You didn't say there would never be more than 80 rows, you said you were
having problems and that might have been another reason, apart from the
obvious. If you are 100% certain you will never refer to a row over 32k then
yes you could use Integer. But one day it might bite you.

In 32 bit vba there's no advantage to using an Integer vs a Long.

Regards,
Peter T

Peter T wrote:
"davegb" wrote in message
oups.com...

Peter T wrote:
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

Why not interger with rows if there's never going to be more than 80 of
them?

You didn't say there would never be more than 80 rows, you said you were
having problems and that might have been another reason, apart from the
obvious. If you are 100% certain you will never refer to a row over 32k then
yes you could use Integer. But one day it might bite you.

In 32 bit vba there's no advantage to using an Integer vs a Long.

Regards,
Peter T

Thanks for the reply! I doubt that my state will ever have 32,000+
counties! :)
Seriously, does this mean that "integer" as a data type is obsolete? Is
there any use for it anymore?

<snip
It would be a good idea to qualify your variables
Dim iOrigCityNo as long, iEndRow As Long ' not Integer with rows

Why not interger with rows if there's never going to be more than 80
of
them?

You didn't say there would never be more than 80 rows, you said you were
having problems and that might have been another reason, apart from the
obvious. If you are 100% certain you will never refer to a row over 32k
then
yes you could use Integer. But one day it might bite you.

In 32 bit vba there's no advantage to using an Integer vs a Long.

Regards,
Peter T

Thanks for the reply! I doubt that my state will ever have 32,000+
counties! :)
Seriously, does this mean that "integer" as a data type is obsolete? Is
there any use for it anymore?


It's a good point and fears have been expressed about the Integer data type
becoming obsolete. Though I doubt it will in the foreseeable future.

My understanding, gleaned from others, is in 32 bit vba Integer types are
internally converted to Long's before any work is done. In other words an
additional process and overhead.

That being the case there seems no point ever to use an Integer, except
perhaps when a particular inbuilt function specifically expects an Integer.
But even these functions still seem to work fine if a Long is received.

Regards,
Peter T