I've tried to research this on the net, but couldn't find anything that
directly deals with my confusion. That is, what does With/End With do
different than using the object name and the appropriate code?
Walkenbach says it will save running time. Elsewhere I read that it
saves entry time. But it must do more than that. For example, some code
I just wrote, when I used the spreadsheet name, wouldn't run. When I
used With/End with, it worked.

set rCTyLst=wsCtyLst.Range(.Cells(2, sCtyLstCol), .Cells(lRow,
sCtyLstCol))

This gives an "invalid or unqualified reference error"

With wsCtyLst
Set rCtyLst = .Range(.Cells(2, sCtyLstCol), .Cells(lRow,
sCtyLstCol))
End With

This code runs. Very confusing. Why?
Thanks in advance.

At a glance it looks like you need to remove the dot from in front of
"Cells", but I haven't tested it.

set rCTyLst=wsCtyLst.Range(Cells(2, sCtyLstCol), Cells(lRow, sCtyLstCol))


"davegb" wrote:

I've tried to research this on the net, but couldn't find anything that
directly deals with my confusion. That is, what does With/End With do
different than using the object name and the appropriate code?
Walkenbach says it will save running time. Elsewhere I read that it
saves entry time. But it must do more than that. For example, some code
I just wrote, when I used the spreadsheet name, wouldn't run. When I
used With/End with, it worked.

set rCTyLst=wsCtyLst.Range(.Cells(2, sCtyLstCol), .Cells(lRow,
sCtyLstCol))

This gives an "invalid or unqualified reference error"

With wsCtyLst
Set rCtyLst = .Range(.Cells(2, sCtyLstCol), .Cells(lRow,
sCtyLstCol))
End With

This code runs. Very confusing. Why?
Thanks in advance.

A "dot" is used to join code together.
In the first line of code you have a dot in front of "Cells" but
nothing to join it with.

In the second example, wsCtyLst is joined with Cells by using the With statement.

Using the With statement can speed up code only if it reduces
the number of dots used.

So this is an improvement...
With Worksheets(1).Cells(1, 1).Interior
.ColorIndex = 15
.PatternColor = 5
End With

This is not...
With Cells(2, 2)
.Value = "Test"
.Font.Bold = True
End With

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"davegb" wrote in message oups.com...
I've tried to research this on the net, but couldn't find anything that
directly deals with my confusion. That is, what does With/End With do
different than using the object name and the appropriate code?
Walkenbach says it will save running time. Elsewhere I read that it
saves entry time. But it must do more than that. For example, some code
I just wrote, when I used the spreadsheet name, wouldn't run. When I
used With/End with, it worked.

set rCTyLst=wsCtyLst.Range(.Cells(2, sCtyLstCol), .Cells(lRow,
sCtyLstCol))

This gives an "invalid or unqualified reference error"

With wsCtyLst
Set rCtyLst = .Range(.Cells(2, sCtyLstCol), .Cells(lRow,
sCtyLstCol))
End With

This code runs. Very confusing. Why?
Thanks in advance.

The with statement gives you a reference to an object. In your case it is
giving you reference to a worksheet. Charlie suggests removing the dots,
which will remove the syntax error but will quite likely give you a problem.
If you do not reference a worksheet then you are implicitly referencing the
active sheet (whatever that might be, and very possibly not the sheet you
intend. IMO this is dangerous code which is prone to fail if you alter the
code ahead of it to select different sheets).

You could write the code you have as this with out the with statement...

Set rCtyLst = wsCtyLst.Range(wsCtyLst.Cells(2, sCtyLstCol),
wsCtyLst.Cells(lRow,
sCtyLstCol))

As you can see "with" makes it more compact and (very marginally) should
make it run faster as the computer does not need to keep grabbing new
references to the worksheet. What Charlie has suggest would translate to this

Set rCtyLst = activesheet.Range(activesheet.Cells(2, sCtyLstCol),
activesheet.Cells(lRow, sCtyLstCol))

Which you can see is quite different and as I suggested earlier prone to
failure if you change what sheet is the active sheet prior to running this
code.

With out the with statement you have a syntax error because it does not know
what object you are trying to reference with the dot...

The long and the short is go with the with statement. That line of code is
relatively bulletproof and very efficient. I have noticed that you code is
much improved over the past few months. Keep it up and keep asking the good
questions.

--
HTH...

Jim Thomlinson


"davegb" wrote:

I've tried to research this on the net, but couldn't find anything that
directly deals with my confusion. That is, what does With/End With do
different than using the object name and the appropriate code?
Walkenbach says it will save running time. Elsewhere I read that it
saves entry time. But it must do more than that. For example, some code
I just wrote, when I used the spreadsheet name, wouldn't run. When I
used With/End with, it worked.

set rCTyLst=wsCtyLst.Range(.Cells(2, sCtyLstCol), .Cells(lRow,
sCtyLstCol))

This gives an "invalid or unqualified reference error"

With wsCtyLst
Set rCtyLst = .Range(.Cells(2, sCtyLstCol), .Cells(lRow,
sCtyLstCol))
End With

This code runs. Very confusing. Why?
Thanks in advance.

Jim Thomlinson wrote:
The with statement gives you a reference to an object. In your case it is
giving you reference to a worksheet. Charlie suggests removing the dots,
which will remove the syntax error but will quite likely give you a problem.
If you do not reference a worksheet then you are implicitly referencing the
active sheet (whatever that might be, and very possibly not the sheet you
intend. IMO this is dangerous code which is prone to fail if you alter the
code ahead of it to select different sheets).

You could write the code you have as this with out the with statement...

Set rCtyLst = wsCtyLst.Range(wsCtyLst.Cells(2, sCtyLstCol),
wsCtyLst.Cells(lRow,
sCtyLstCol))

As you can see "with" makes it more compact and (very marginally) should
make it run faster as the computer does not need to keep grabbing new
references to the worksheet. What Charlie has suggest would translate to this

Set rCtyLst = activesheet.Range(activesheet.Cells(2, sCtyLstCol),
activesheet.Cells(lRow, sCtyLstCol))

Which you can see is quite different and as I suggested earlier prone to
failure if you change what sheet is the active sheet prior to running this
code.

With out the with statement you have a syntax error because it does not know
what object you are trying to reference with the dot...

Thanks to everyone who replied!
This makes it much clearer, Jim.


The long and the short is go with the with statement. That line of code is
relatively bulletproof and very efficient. I have noticed that you code is
much improved over the past few months. Keep it up and keep asking the good
questions.

--
HTH...

Jim Thomlinson

Thanks for the encouragement. Some days, this gets pretty frustrating!
While the macro now runs, it doesn't do what I want. I'm going to
change some of the variable names to make it clearer and post another
question in a separate thread.



"davegb" wrote:

I've tried to research this on the net, but couldn't find anything that
directly deals with my confusion. That is, what does With/End With do
different than using the object name and the appropriate code?
Walkenbach says it will save running time. Elsewhere I read that it
saves entry time. But it must do more than that. For example, some code
I just wrote, when I used the spreadsheet name, wouldn't run. When I
used With/End with, it worked.

set rCTyLst=wsCtyLst.Range(.Cells(2, sCtyLstCol), .Cells(lRow,
sCtyLstCol))

This gives an "invalid or unqualified reference error"

With wsCtyLst
Set rCtyLst = .Range(.Cells(2, sCtyLstCol), .Cells(lRow,
sCtyLstCol))
End With

This code runs. Very confusing. Why?
Thanks in advance.

set rCTyLst=wsCtyLst.Range( _
wsCtyLst.Cells(2, sCtyLstCol), _
wsCtyLst.Cells(lRow,sCtyLstCol))




With wsCtyLst
Set rCtyLst = .Range(.Cells(2, sCtyLstCol), _
.Cells(lRow,sCtyLstCol))
End With

The two are equivalent.

As Jim Cone demonstrated, in this case using with would be for convenience -
less duplicate code to write.

or
set rCtyLst = wsCtyLst.Cells(2,sCtyLstCol).Resize(lrow-1,1)

would avoid both With and duplicate code.

--
Regards,
Tom Ogilvy


"davegb" wrote in message
oups.com...
I've tried to research this on the net, but couldn't find anything that
directly deals with my confusion. That is, what does With/End With do
different than using the object name and the appropriate code?
Walkenbach says it will save running time. Elsewhere I read that it
saves entry time. But it must do more than that. For example, some code
I just wrote, when I used the spreadsheet name, wouldn't run. When I
used With/End with, it worked.

set rCTyLst=wsCtyLst.Range(.Cells(2, sCtyLstCol), .Cells(lRow,
sCtyLstCol))

This gives an "invalid or unqualified reference error"

With wsCtyLst
Set rCtyLst = .Range(.Cells(2, sCtyLstCol), .Cells(lRow,
sCtyLstCol))
End With

This code runs. Very confusing. Why?
Thanks in advance.

Tom Ogilvy wrote:
set rCTyLst=wsCtyLst.Range( _
wsCtyLst.Cells(2, sCtyLstCol), _
wsCtyLst.Cells(lRow,sCtyLstCol))




With wsCtyLst
Set rCtyLst = .Range(.Cells(2, sCtyLstCol), _
.Cells(lRow,sCtyLstCol))
End With

The two are equivalent.

As Jim Cone demonstrated, in this case using with would be for convenience -
less duplicate code to write.

or
set rCtyLst = wsCtyLst.Cells(2,sCtyLstCol).Resize(lrow-1,1)

would avoid both With and duplicate code.

--
Regards,
Tom Ogilvy

Thanks Tom. I appreciate your comments about writing solid code. I can
see in this example where there could be trouble later if the
ActiveSheet changes.



"davegb" wrote in message
oups.com...
I've tried to research this on the net, but couldn't find anything that
directly deals with my confusion. That is, what does With/End With do
different than using the object name and the appropriate code?
Walkenbach says it will save running time. Elsewhere I read that it
saves entry time. But it must do more than that. For example, some code
I just wrote, when I used the spreadsheet name, wouldn't run. When I
used With/End with, it worked.

set rCTyLst=wsCtyLst.Range(.Cells(2, sCtyLstCol), .Cells(lRow,
sCtyLstCol))

This gives an "invalid or unqualified reference error"

With wsCtyLst
Set rCtyLst = .Range(.Cells(2, sCtyLstCol), .Cells(lRow,
sCtyLstCol))
End With

This code runs. Very confusing. Why?
Thanks in advance.

You code, yest. But not with the code I posted. It should work regardless
of the activesheet if wsCtyLst is a reference to a valide worksheet.

--
Regards,
Tom Ogilvy

"davegb" wrote in message
ps.com...

Tom Ogilvy wrote:
set rCTyLst=wsCtyLst.Range( _
wsCtyLst.Cells(2, sCtyLstCol), _
wsCtyLst.Cells(lRow,sCtyLstCol))




With wsCtyLst
Set rCtyLst = .Range(.Cells(2, sCtyLstCol), _
.Cells(lRow,sCtyLstCol))
End With

The two are equivalent.

As Jim Cone demonstrated, in this case using with would be for
convenience -
less duplicate code to write.

or
set rCtyLst = wsCtyLst.Cells(2,sCtyLstCol).Resize(lrow-1,1)

would avoid both With and duplicate code.

--
Regards,
Tom Ogilvy

Thanks Tom. I appreciate your comments about writing solid code. I can
see in this example where there could be trouble later if the
ActiveSheet changes.



"davegb" wrote in message
oups.com...
I've tried to research this on the net, but couldn't find anything
that
directly deals with my confusion. That is, what does With/End With do
different than using the object name and the appropriate code?
Walkenbach says it will save running time. Elsewhere I read that it
saves entry time. But it must do more than that. For example, some
code
I just wrote, when I used the spreadsheet name, wouldn't run. When I
used With/End with, it worked.

set rCTyLst=wsCtyLst.Range(.Cells(2, sCtyLstCol), .Cells(lRow,
sCtyLstCol))

This gives an "invalid or unqualified reference error"

With wsCtyLst
Set rCtyLst = .Range(.Cells(2, sCtyLstCol), .Cells(lRow,
sCtyLstCol))
End With

This code runs. Very confusing. Why?
Thanks in advance.