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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

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?
 

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.
 

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.]
 

bill

Well-known Member
Joined
Mar 7, 2002
Messages
550

ADVERTISEMENT

If I read you correctly, you're asking if the range is specific to the sheet.
Yes, else example 2 would not have worked.
 

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.
 

bill

Well-known Member
Joined
Mar 7, 2002
Messages
550

ADVERTISEMENT

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...
 

NateO

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

Does the following:

Dim QueryRange As Range
Set QueryRange = [SAPBEXq0001]

?
 

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?
 

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]
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,491
Messages
5,764,681
Members
425,229
Latest member
Rashid mahmood

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