Named Range Fails in VBA Code

[Guest]

Hello All,

I have a problem that's driving me nuts. I have a worksheet named 'Audit
Data' layed out like this...

Profit Center
Period 712927 713927 716927 722927
01/2005 80.00% 80.00% 80.00% 80.00%
02/2005
03/2005
04/2005
05/2005
06/2005

Then the named range ('Audit_Data') has a formula like this.....
=OFFSET('Audit Data'!A$2,0,0,COUNTA('Audit Data'!$A:$A),COUNTA('Audit
Data'!$2:$2))

When I check the named range in the worksheet, it selects the area
correctly. However, when I try to use the name range in VBA, I get an error
1004 (Method Range of Object failed). I have deleted the worksheet, thinking
corruped sheet, and added a new one. I have changed the name of the worksheet
and name range to a variety of different names and still the same error. But
one thing I found even more puzzling, I changed the formula to do the same
thing to a different worksheet of data, and the VBA worked. What am I
missing? What tree am I not seeing in this forrest?

Thank you in advance for any insight.

Dean.

 

[Bob Phillips]

Dean,

It worked okay for me. I typed this in the immediate window

range("audit_data").Select

and the range was selected fine.

What is your code that uses this range?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Hey RP,

In order to determine my problem, I was using
X=Application.Range("Audit_Data") to see if the range was good in VBA.
However, I decided to create a copy of the named range (Named it
"Audit_DataX") and point it to another worksheet like I did earlier. Then I
added code Y=Application.Range("Audit_DataX") and tried the code again.
Well, they both now work. So I am leaving it as is. One day I'll figure out
what the glitch was, or maybe not.

Thanks for the reply. I know this is not the proper solution, but I have to
get this done tomorrow and I'm a little behind.

Regards, Dean.

 

[Bob Phillips]

Dean,

Did you previously define X as a range by any chance? If you did, the
statement

X= Range("Audit_Data")

fails as objects need to be set

Set X = Range("Audit_Data")

If you subsequently left X and Y declared as no particular types, or even
didn't declare them, as variants the statement

X = Range("Audit_Data"0

would load an array X with all the values in the range.

--

HTH

RP
(remove nothere from the email address if mailing direct)