Ranges in code

bill

Well-known Member
Joined
Mar 7, 2002
Messages
550
Why does this fail?
Code:
Dim QueryRange As Range
  Set QueryRange = SAP02.Range("SAPBEXq0001")

but this not?
Code:
Dim QueryRange As Range
  Set QueryRange = Range("SAPBEXqueries!SAPBEXq0001")

Yes, SAP02 is the codenae for worksheet "SAPBEXqueries"
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
Both work fine for me. What is the error message you're getting and which version of Excel are you using?
 
Upvote 0

bill

Well-known Member
Joined
Mar 7, 2002
Messages
550
Method 'Range of object'_worksheet failed

Tried it again and same result... No Go...

My version of XLS is 97.
 
Upvote 0

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
Of course I can't do it now when I want to, and my Walkenbach book is at home... But I recall times where my named ranges have been worksheet specific, i.e. the sheet name was prepended to the range name. If you go to Insert | Name | Define... does that named range have the sheet name prepended to it? [Just taking a guess here - 'cause just looking at the code, it appears that it should work.]
 
Upvote 0

bill

Well-known Member
Joined
Mar 7, 2002
Messages
550
If I read you correctly, you're asking if the range is specific to the sheet.
Yes, else example 2 would not have worked.
 
Upvote 0

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
Sorry, I'm not being very articulate. And I was able to re-create the situation I described. Normally, if you create a named range Excel makes it "global". But, for example, if you use Insert | Name | Create... to generate named ranges that will be the same as named ranges that already exist, Excel will place some type of "qualifier" on that particular range, making it specific to that one worksheet. If you go to the Insert | Name | Define... dialog box, over to the right of the named range, the worksheet name appears. And if you try to pick the name from the box on the left of the formula bar, it only goes to the range on that WS, if that WS is active. Again, this was a just a long-shot thought, since your code appears to look good.
 
Upvote 0

bill

Well-known Member
Joined
Mar 7, 2002
Messages
550
Um, I suppose we're on the same page and if so, my last answer applies.

The range is specific to the sheet (not global).

Getting the feeling I did not ask the question right...
 
Upvote 0

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
Seems like it should work...

Does the following:

Dim QueryRange As Range
Set QueryRange = [SAPBEXq0001]

?
 
Upvote 0

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
Hi,

What happens when you run this code:-

Code:
Sub EnumNamedRanges()
Dim n As Name

For Each n In SAP02.Names
Debug.Print n.Name; n.RefersTo
Next n

End Sub

If you look at the immediate window do you see the SAPBEXq0001 named range listed?
 
Upvote 0

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
If you run this, do you get what you'd expect?

<font face=Courier New>
<SPAN style="color:#00007F">Sub</SPAN> listnames()
    <SPAN style="color:#00007F">Dim</SPAN> n <SPAN style="color:#00007F">As</SPAN> Name
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> n <SPAN style="color:#00007F">In</SPAN> Names
        Debug.Print n.Name, n.Parent.Name, n.Parent.CodeName
    <SPAN style="color:#00007F">Next</SPAN> n
        
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

[Edit]See, this is what happens when I answer the phone while in the middle of typing a post --- dk beats me to the punch! :biggrin: [EndEdit]
 
Upvote 0

Forum statistics

Threads
1,186,808
Messages
5,959,933
Members
438,455
Latest member
Beverly Jarrell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top