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.