Range Name issue

G

Guest

I have created a named range with a single value in it and I can't seem to
access it within my VBA code. This is what I have so far. I've put it in
a debug.print statement to see if I can see what's going on and it gacks each
time.

Debug.Print Range("P_bar").Text
Debug.Print Range("P_bar").Value

I get a

Runtime error '1004'
Method 'range' of object '_Global' failed.

I can access the named range from the worksheet, just not the macro. The
named range is a workbook range, not a worksheet range.

Thanks,
 
G

Guest

The range name definition is

=SUM(Defectives)/SUM(Opportunities)

If that helps at all.
 
S

Sandy

to access a named range w/code use the following:

for accessing on an active sheet
Range("MyRange").Value

access a range on a specific sheet
Range("Sheet1!MyRange").Value

access a range in another open workbook
Range("[Book1.xls]MyRange").Value

access a range on a specific sheet in another workbook
Range("[Book1.xls]Sheet1!MyRange").Value


Hope this helps

Sandy
 
S

Sandy

to access a named range w/code use the following:

for accessing on an active sheet
Range("MyRange").Value

access a range on a specific sheet
Range("Sheet1!MyRange").Value

access a range in another open workbook
Range("[Book1.xls]MyRange").Value

access a range on a specific sheet in another workbook
Range("[Book1.xls]Sheet1!MyRange").Value


Hope this helps

Sandy
 
S

Sandy

to access a named range w/code use the following:

for accessing on an active sheet
Range("MyRange").Value

access a range on a specific sheet
Range("Sheet1!MyRange").Value

access a range in another open workbook
Range("[Book1.xls]MyRange").Value

access a range on a specific sheet in another workbook
Range("[Book1.xls]Sheet1!MyRange").Value


Hope this helps

Sandy
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Finding first value in named range referred to in data validation list 2
Removing spaces from value using VBA 9
Easy code, confounded by error 3
Accessing a macro created named range 7
Problem referring to a particular sheet in VBA 5
Help needed to writing a macro to put the year in column B of a tableusing Table nomenclature & the 7
[Q] How to create new worksheets based on an array (via named range) 4
Post my named ranges in column A 10

Top