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.

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?

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.

"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

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.

Why introduce application in the middle of a With statement. While it may
work, it doesn't make sense to me when the simple inclusion of the period
would suffice.

Obviously, if Daves contribution were visible, I wouldn't have bothered.



--
Regards,
Tom Ogilvy

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

"Tom Ogilvy" wrote in message
Why introduce application in the middle of a With statement. While it
may
work, it doesn't make sense to me when the simple inclusion of the period
would suffice.

It was highly contrived but based on the previous example to further
re-enforce what you and Dave had noted, particularly with regards to what
Range defaults to can be changed. Whilst very familiar to you perhaps not
to everyone. I wouldn't use that particular scenario.

Obviously, if Daves contribution were visible, I wouldn't have bothered.

I always appreciate anyone having taken the trouble to respond to any
question I have raised.

Regards,
Peter T

And I didn't mean to give the impression that application.range is something I
generally use.



Peter T wrote:

"Tom Ogilvy" wrote in message
Why introduce application in the middle of a With statement. While it
may
work, it doesn't make sense to me when the simple inclusion of the period
would suffice.

It was highly contrived but based on the previous example to further
re-enforce what you and Dave had noted, particularly with regards to what
Range defaults to can be changed. Whilst very familiar to you perhaps not
to everyone. I wouldn't use that particular scenario.

Obviously, if Daves contribution were visible, I wouldn't have bothered.

I always appreciate anyone having taken the trouble to respond to any
question I have raised.

Regards,
Peter T

--

Dave Peterson

"Dave Peterson" wrote in message
And I didn't mean to give the impression that application.range is
something I generally use.

I didn't take it that way but your comments were interesting nevertheless.

My first reply to Tom probably expanded on the topic beyond the point of
being useful !

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

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

Interesting series of replies to my original message. I have some
beginner questions.
I gather that "qualifying" means to show what object is the parent of
the current object? If I'm working with a range, is it's parent the
activesheet or the application? Is that what it means?
What is the difference between "Range" and ".Range"? I don't understand
why one works and the other doesn't in different situations.

Dave P. wrote:
Try putting this code behind Sheet1's module.

What does this mean? There are other references above to "Sheet
modules" and "regular modules". What are they? What's the difference?
Why would a ".range" be required in one but a "range" be ok in another?

And why does

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

work better than

set rTOCtyLst = Activesheet.range(.Cells(1,1), .Cells(1, iEndRow))

?

Got lots of questions here, but am trying to figure out if there are
discernable patterns to VBA or if it's just memorizing thousands of
rules that aren't in the books!
I appreciate all the help.

Interesting series of replies to my original message. I have some
beginner questions.

One by one -

I gather that "qualifying" means to show what object is the parent of
the current object? If I'm working with a range, is it's parent the
activesheet or the application? Is that what it means?

Application.Workbooks("myBook").WorkSheets("myShee t").Range("A1")

This tree-like path always exists. Assuming code is in a normal module (see
later), if no reference is made back to parents, VBA works with the
activesheet in the active workbook. So, if you want to work with Range("A1")
on the activesheet, you don't need to reference (qualify) to it's parent
sheet or workbook. Similarly with

Set rng = Range(Cells(1,1),Cells(2,2))

in this line, assuming code is in a normal module, the important implied
reference is to "Cells" in the active sheet. Here, Cells do not and should
not have a preceding dot unless the line is embraced with "With
mySheet...End With". That was the problem in your OP. Why - because the
preceding dot is expected to link to a written reference to the Cells'
parent sheet.

What is the difference between "Range" and ".Range"? I don't understand
why one works and the other doesn't in different situations.

Again, if you include a preceding dot it links to a ref, that you need to
write, to whatever parent you want the range to be "in".
But -
With mySheet
set rng = Range(.Cells(1,1),.Cells(2,2))
End With

Range does not need the preceeding dot as the reference to the parent sheet
is linked with the dots that precede Cells. But I agree with all the
recommendations to include it.

Dave P. wrote:
Try putting this code behind Sheet1's module.

What does this mean? There are other references above to "Sheet
modules" and "regular modules". What are they? What's the difference?
Why would a ".range" be required in one but a "range" be ok in another?

Right-click a sheet-tab, view-code and you wll go straight into a sheet
module. Typical code in a sheet module are sheet events and worksheet
ActiveX control's code. But you can also write your own routines there (but
don't until you understand what you are doing).

Unlike unqualified references in normal modules that default to the
activesheet & workbook, any unqualified code refers to the Worksheet of that
sheet module (whether or not it is active). Therefore if code is not
intended to refer to that sheet you need to explicitly refer whatever other
sheet.

'in a sheet module
With mySheet
set rng = .Range(.Cells(1,1),.Cells(2,2))
End With

Unlike the similar code higher up, the dot preceeding Range is definately
required. Otherwise there is a conflict between Range (referring to the
sheet module) and Cells (referring) to mySheet.

Hope this takes you a bit further,
Peter T

Just this portion:

With mySheet
set rng = Range(.Cells(1,1),.Cells(2,2))
End With

Range does not need the preceeding dot as the reference to the parent sheet
is linked with the dots that precede Cells. But I agree with all the
recommendations to include it.

======
It still depends on where that code is located.

======
And for me, Integer as a variable type is gone.


<<snipped
--

Dave Peterson

I had attempted to qualify that portion with -
"Assuming code is in a normal module (see later)" etc

Then later I tried to make the clear distinction that the dot ref is
definitely required in a sheet module, if Range does not pertain to that
sheet.

But it did mean scrolling down to see it <g

Regards,
Peter T


"Dave Peterson" wrote in message
...
Just this portion:

With mySheet
set rng = Range(.Cells(1,1),.Cells(2,2))
End With

Range does not need the preceeding dot as the reference to the parent
sheet
is linked with the dots that precede Cells. But I agree with all the
recommendations to include it.

======
It still depends on where that code is located.

======
And for me, Integer as a variable type is gone.


<<snipped
--

Dave Peterson

Hey, how did that part get added after I read it!!!! <bg.

Sorry about missing your final paragraph.

Peter T wrote:

I had attempted to qualify that portion with -
"Assuming code is in a normal module (see later)" etc

Then later I tried to make the clear distinction that the dot ref is
definitely required in a sheet module, if Range does not pertain to that
sheet.

But it did mean scrolling down to see it <g

Regards,
Peter T

"Dave Peterson" wrote in message
...
Just this portion:

With mySheet
set rng = Range(.Cells(1,1),.Cells(2,2))
End With

Range does not need the preceeding dot as the reference to the parent
sheet
is linked with the dots that precede Cells. But I agree with all the
recommendations to include it.

======
It still depends on where that code is located.

======
And for me, Integer as a variable type is gone.


<<snipped
--

Dave Peterson

--

Dave Peterson

Peter T wrote:
Interesting series of replies to my original message. I have some
beginner questions.

One by one -

I gather that "qualifying" means to show what object is the parent of
the current object? If I'm working with a range, is it's parent the
activesheet or the application? Is that what it means?

Application.Workbooks("myBook").WorkSheets("myShee t").Range("A1")

This tree-like path always exists. Assuming code is in a normal module (see
later), if no reference is made back to parents, VBA works with the
activesheet in the active workbook. So, if you want to work with Range("A1")
on the activesheet, you don't need to reference (qualify) to it's parent
sheet or workbook. Similarly with

Set rng = Range(Cells(1,1),Cells(2,2))

in this line, assuming code is in a normal module, the important implied
reference is to "Cells" in the active sheet. Here, Cells do not and should
not have a preceding dot unless the line is embraced with "With
mySheet...End With". That was the problem in your OP. Why - because the
preceding dot is expected to link to a written reference to the Cells'
parent sheet.

What is the difference between "Range" and ".Range"? I don't understand
why one works and the other doesn't in different situations.

Again, if you include a preceding dot it links to a ref, that you need to
write, to whatever parent you want the range to be "in".
But -
With mySheet
set rng = Range(.Cells(1,1),.Cells(2,2))
End With

Range does not need the preceeding dot as the reference to the parent sheet
is linked with the dots that precede Cells. But I agree with all the
recommendations to include it.

Dave P. wrote:
Try putting this code behind Sheet1's module.

What does this mean? There are other references above to "Sheet
modules" and "regular modules". What are they? What's the difference?
Why would a ".range" be required in one but a "range" be ok in another?

Right-click a sheet-tab, view-code and you wll go straight into a sheet
module. Typical code in a sheet module are sheet events and worksheet
ActiveX control's code. But you can also write your own routines there (but
don't until you understand what you are doing).

Unlike unqualified references in normal modules that default to the
activesheet & workbook, any unqualified code refers to the Worksheet of that
sheet module (whether or not it is active). Therefore if code is not
intended to refer to that sheet you need to explicitly refer whatever other
sheet.

'in a sheet module
With mySheet
set rng = .Range(.Cells(1,1),.Cells(2,2))
End With

Unlike the similar code higher up, the dot preceeding Range is definately
required. Otherwise there is a conflict between Range (referring to the
sheet module) and Cells (referring) to mySheet.

Hope this takes you a bit further,
Peter T

Thanks to everyone for your replies. It's definitely helping me to
figure out VBA. Thanks, Peter, for you detailed explanations of the
why's and how's.
One more question, for now. Are ActiveX controls for controlling other
apps?

"davegb" wrote in message
<<snip

Thanks to everyone for your replies. It's definitely helping me to
figure out VBA. Thanks, Peter, for you detailed explanations of the
why's and how's.
One more question, for now. Are ActiveX controls for controlling other
apps?

Worksheet ActiveX controls are applied to a sheet from the Controls Toolbox
menu (Main menu, View, Toolbars). They respond to "Events" such as Click,
from these you can do or control what you want. The event code is in the
sheet module. Drag a button onto the sheet, while still in Design mode*
select View code* (* icons on the menu).

There are also controls from the "Forms" menu which don't respond to events,
other than OnAction when assigned to a macro.

There is often confusion between the two types. If you can't find what you
need to know about these by searching the groups or in help, it would be
better to start a new topic. In .Misc for general info and here for
specifics about programming.

There are of course 000's of other ActiveX controls, it'd be nice to find
one that makes a decent cup of coffee.

Regards,
Peter T

Peter T wrote:
"davegb" wrote in message
<<snip

Thanks to everyone for your replies. It's definitely helping me to
figure out VBA. Thanks, Peter, for you detailed explanations of the
why's and how's.
One more question, for now. Are ActiveX controls for controlling other
apps?

Worksheet ActiveX controls are applied to a sheet from the Controls Toolbox
menu (Main menu, View, Toolbars). They respond to "Events" such as Click,
from these you can do or control what you want. The event code is in the
sheet module. Drag a button onto the sheet, while still in Design mode*
select View code* (* icons on the menu).

There are also controls from the "Forms" menu which don't respond to events,
other than OnAction when assigned to a macro.

There is often confusion between the two types. If you can't find what you
need to know about these by searching the groups or in help, it would be
better to start a new topic. In .Misc for general info and here for
specifics about programming.

There are of course 000's of other ActiveX controls, it'd be nice to find
one that makes a decent cup of coffee.

Regards,
Peter T

Thanks again. As for the cup of coffee, I'm allergic to the stuff. But
a good marguerita...