select method of range class failed error

[johndough185]

I have different variables that I'm using to define ranges within different sheets. Whenever I switch from Sheet A to Sheet B, however, and then try to select a range in Sheet A by the name that I gave it in the vba, I get the "select method of range class failed error".

I assume it has something to do with the fact that I'm giving different ranges in different sheets names, and then referring back to them from different sheets. Is there a way to define the name of the range so that it doesn't disappear once I switch to a different sheet? I just put

Dim a As Range

where "a" is the variable for the range that I set later in the code using "Set".

[jasoncw]

Without seeing your code, I would guess that you are not qualifying the range enough. You are probably using:

Please Login or Register  to view this content.

You should be using:

Please Login or Register  to view this content.

Or better yet:

Please Login or Register  to view this content.

HTH

Jason

[Leith Ross]

Hello johndough185,

This is a scoping problem. When you Dim in a private Sub or Function procedure, the variable will be destroyed once the procedure code is finished. To have a Range persist for the time Workbook is open, you should delcare a named Range. Use the Name property of the Range to assign it a name.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[johndough185]

I'm actually using

Set a = Selection

and it doesn't look like specifying the sheet before Selection works. I get the "object doesn't support this property or method" error. any other ideas?

[jasoncw]

Did you read Leith's response? That would also definitely be an issue. Is this in a Private sub? If it is, the variable cannot be passed between procedures.

Jason