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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Both work fine for me. What is the error message you're getting and which version of Excel are you using?
 
Upvote 0
Method 'Range of object'_worksheet failed

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

My version of XLS is 97.
 
Upvote 0
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
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
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
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
Seems like it should work...

Does the following:

Dim QueryRange As Range
Set QueryRange = [SAPBEXq0001]

?
 
Upvote 0
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
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,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

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
Back
Top