Named Range Fails in VBA Code

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.

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)


"Dean Hinson" <DeanHinson@discussions.microsoft.com> wrote in message
news:17090B04-4E86-4569-B8B0-943C16B8E895@microsoft.com...
> 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.

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" wrote:

> 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)
>
>
> "Dean Hinson" <DeanHinson@discussions.microsoft.com> wrote in message
> news:17090B04-4E86-4569-B8B0-943C16B8E895@microsoft.com...
> > 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.
>
>
>

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)


"Dean Hinson" <DeanHinson@discussions.microsoft.com> wrote in message
news:D331511A-5950-497B-A854-3241F9358F75@microsoft.com...
> 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" wrote:
>
> > 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)
> >
> >
> > "Dean Hinson" <DeanHinson@discussions.microsoft.com> wrote in message
> > news:17090B04-4E86-4569-B8B0-943C16B8E895@microsoft.com...
> > > 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.
> >
> >
> >