Error when selecting a range

[boylejob]

I have a program that creates and prints a form. A certain portion of this form has borders around the cells. After joining this forum I am breaking out of "selecting" things, but I am having trouble on this on.


The code below is giving me a 1004 error.

What am I doing wrong? I have just started trying to use ranges so I am not real clear yet on the coding.

Any help will certainly be appreciated.

Jeff

[boylejob]

Well, I have answered my own question.

Changed

to

and now it works.

This was a small section of a huge sub and I am in the middle of converting it over to not use "select" . Apparently, wsLetter did not have focus and this was causing the problem.

Jeff

[mudraker]

boylejob

You need to be careful using code like

Please Login or Register  to view this content.

as Excel can take the value of the cell and not the cell address, which is what you need for the range part of the command

I recommend you use

Please Login or Register  to view this content.

[boylejob]

Mudraker,

Thanks for the additional help. I would not have even thought about the Address thing. You never know what new things you are going to learn when you post something.

I have one more question on this subject. Like I mentioned, the bit of code I posted is a small portion of a rather large subroutine. The statement I am working on has actually ended up looking like this:

Please Login or Register  to view this content.

Is there any problem with writing the Set statement as I have it listed above. Basically, incorporating it within a With statement.

I have learned a great deal from this forum so I am trying things I have not tried in the past. The speed at which my programs are now running has greatly improved as a result of the stuff I am learning on the forum.

Thanks!

Jeff

[mudraker]

Jeff

I have never used set within a With something command util your previous posting

I tried this code.
It set the range as coded
So I guess the answer is no there is no problem
I will ask another more experienced person to have a look at this thread

Please Login or Register  to view this content.

[Leith Ross]

Hello Jeff,

The With statement is a very efficient method of writing code. It also makes your code much easier to read.This VBA statement is identical to the "C" language statement. It speeds compiliation of the code by the interpreter. When you use a statement like Range("A10").Select the interpreter must find the object parent first. The With statement provides the interpreter with a short cut. The interpreter looks up the parent object only once. It finds the parent of the object following With and retains it until the End With is encountered.

You can even nest With statements. A good example is setting Font properties and Border properties.

Please Login or Register  to view this content.

Hope this helps you to better understand the With statement.

Sincerely,
Leith Ross

[boylejob]

Leith,

Thank you for the explanation. That helps me understand what exactly is going on.

I have been programming in VBA for about 6-7 years now. Pretty much self taught by picking up things from here and there. After joining this forum I have found I have done a lot of things the hard way. I have used what I understood and worked around that which I did not. I am learning new things everyday from this forum.

Yesterday, I put the "with" statement to use to a greater extent than I have in the past and I wanted to make sure there is no problem with the way I have this code. The program I am working on is a pretty important program in my office and I want to be sure I haven't fouled things up.

My concern was with my Set rletter statement that is buried deep within a With statement. As you can see by Mudraker's post he set up a bit of code similar to mine and ran it. It worked fine on his end and it worked fine on my end. The code I have below is an expansion of Mudraker's code, but I have it set exactly like it is in my program (just a shortened version).

Please Login or Register  to view this content.

This is the statement I was concerned with

Please Login or Register  to view this content.

Do you see any problems with this statement? It just had a lot more periods than I am accustom to seeing so I wanted to make sure it is correct and will not cause me a problem later.

Thanks!

Jeff

[Leith Ross]

Hello Jeff,

The Set statement simply assigns an object variable to an existing object. In your case rLetter is now tha same object as ...

Please Login or Register  to view this content.

Since this is what you intended it to be, no problem. I grew up in Asheville, N.C.

Sincerely,
Leith Ross

[boylejob]

Leith,

Thanks for the help! This forum is a fantastic resource that I sure wish I had found years ago. My code is running so much faster it is absolutely incredible.

I am a transplant from Owensboro, KY and have lived in Forest City for about 5 years now which is about an hour from Ashville. Small world and the internet seems to be making it even smaller.

Jeff