Selecting Named Range in Vba

[Brijesh]

I have defined a named range "Wdwl1" in excel 2007
I want to select this range in vba and perform operations on this selction
I m using the following code

[Wdwl1].Select

It gives error msg "Select method of Range class failed"

I also tried this code

Sheets("1").Range("Wdwl1").Select

but getting the same error. I tried the name of the range and it is correctly spelled and also some other operations are performing well on the same range.

Plz suggest

Thanks in advance

 

[Smallman]

The best advice you can get is to not select your named range at all. What are you attempting to do? VBA is a language where you can ask for things to occur without the cursor moving. This ensures your code runs more smoothly. Excel users new tovb see the recorder selecting or activating everything and think this is the way vba should manage the process. You would be better off restating what you are trying to do and having a close look at the efficient code which comes from this question.

By the way it is;

Sheets(1)

or

Sheet1

Take care

Smallman

 

[Brijesh]

Thanks Small Man

I m trying to find out the address of first and last cell of Range "Wdwl1" and i m using following code

[Wdwl1].Select
With Selection
StrAdd = .Cells(1, 1).Address
EndAdd = .Cells(.Rows.Count, .Columns.Count).Address
End With

The worksheet on which this range is defined bears name "1". So i also used the following code

Sheets("1").Range("Wdwl1").Select
With Selection
StrAdd = .Cells(1, 1).Address
EndAdd = .Cells(.Rows.Count, .Columns.Count).Address
End With

Plz suggest what alternate code can be used. Also can you suggest what is wrong with my code due to which it is displaying error?

 

[NARAYANK991]

Hi Brijesh ,

You cannot select a cell or a range on a sheet which is not the active sheet.

If the named range refers to a cell or a range of cells which are on say Sheet1 , and at present the active sheet is Sheet2 , then issuing the above command will return the error you have mentioned.

You need to activate the sheet first ; after that if you issue the above command as :

[Wdwl1].Select

it will work.

Narayan

 

[NARAYANK991]

Hi Brijesh ,

Just :

[Wdwl1].Cells(1,1).Address

will give you the address of the top left cell of the range.

[Wdwl1].Cells([Wdwl1].Rows.Count,[Wdwl1].Columns.Count).Address

will give you the address of the bottom right cell of the range.

Narayan

 

[Smallman]

Plz suggest what alternate code can be used.

Your code does not suggest what you are trying to do. What do you want to do on Sheet 1. Once again you should not be Selecting anything. Just let us know what you are trying do do and you will see how it can be done without selection.

Smallman

 

[Brijesh]

Thanks Narayan

The code works after activating sheet.

Now I have other problem in another vba coding.

I Have two sheets in an excel workbook: Sheet1 and Sheet2.
I have defined names of three cells on sheet1 as "SA", "PPT" and "AGE". On the same sheet 1, name of a range of cells (containing values) is defined as "Range1". Value of "SA" is to be taken from range "Range1" (and to be looped for different values in range) and value of "PPT" and "AGE" is to be taken from some cells on sheet 2 (these are also to be looped). I am using following code:

i= 1 to [Range1].Row.Count

[SA] = [Range1].Cells(i,1).Value

For x=2 to 10
For y = 5 to 15

Sheets("Sheet2").Select
Age1=cells(x,4).value
PPT1=cells(1,y).Value

[Age]=Age1
[PPT]=PPT1
---------
---------
---------
next y
next x
next i

Now "Age", "SA", "PPT" are to be used in calculations as well as their values are to be put on sheet 2. Values of "Age" and "PPT" used in calculation and put on sheet 2 are correct. The value of "SA" is correctly put on sheet 2 but the value of "SA" used in calculation is not correct. Infact the value of "SA" is not assigned on Sheet 1 (which is used in calculations) by codes and earlier value given to the corresponding cell (to which name "SA" is assigned") by old user remains unchanged even after running codes. Plz suggest

 

[NARAYANK991]

Hi Brijesh ,

I am not able to visualize your problem ; if you can upload your file that would be ideal ; if not , do the following so that I can copy your code and try it out :

1. Post the definitions of the 4 ranges

2. Post the data in the various cells which are used in your code.

Narayan

 

[Brijesh]

Hi Narayan
I cud not upload the original file due to secrecy reasons but i m attaching another file which will give you synopsis of what i m trying to do.
In the attached file there are three sheets 1, 2 and 3.
On sheet "1" cell C4 is named as SA, C5 as Age, C6 as PPT and range B16 to B20 as Range1.
On sheet "2" a table is given in which some percentage are defined based on Age and Term.
On sheet "3" there are several tables for several SA.
Now what i have to do is that i have to put choose values from Range1 and put these as SA one by one. For each SA I have to pick combination of Age and PPT from table on sheet 2 and put it on sheet 1 in the cells named as "Age" and "PPT". Now for each combination of Age and PPT I have to find out the percentage form the table on sheet 2 and multiply it by SA and this will give the value of BENEFIT for that Age and PPT for given SA. Now I have to put this particular SA on sheet 3 above the first table and then the calculated BENEFIT for each combination of Age and PPT is to be put in corresponding cell in first table on sheet 3. This whole process is to be repeated for each SA (means the values in the Range1) and in this way i have to fill all tables on sheet 3, each for a particular SA.

The actual calculation is not as simple as this. Its very complicated but the vba part is almost the same (means to take a value of SA from a range and then a combination of Age and PPT and put it on the sheet 1. Thereafter most of the calculations is to be done in excel itself, only a part of calculation is done in vba. Then finally SA and the corresponding table is to be filled using vba)

Unfortunately, in the file attached, the codes are running perfectly. But in actual file the problem is that the SA is correctly put on sheet 3 above each table but the value of SA in cell C4 on sheet 1 is not updated at all. In cell C4 on sheet 1 the value of SA is the old value put by old user and code does not changes it at all. So that the calculated value of BENEFIT is incorrect and so all values put in tables on sheet 3 are incorrect (only SA on the top left of each table is put correctly by code). Plz suggest

 

[NARAYANK991]

Hi Brijesh ,

Thanks. I'll see if I get the time to look into it today , otherwise tomorrow.

Of course , others may chip in and give you the solution today itself.

Narayan

 

[SirJB7]

Hi, Brijesh!
First of all try changing this:
i= 1 to [Range1].Row.Count
to:
For i= 1 to [Range1].Rows.Count
I assume you missed pasting the For but need to use .Rows.Count to loop thru the range, otherwise you'd be going up to the row where the range begins, ... and if it starts at row 1... almost nothing done.
Regards!

 

[NARAYANK991]

Hi Brijesh ,

Can you check your file ?

I have created one more named range ; you can make the changes required to customize the macro to your actual file.

Narayan