Range resize not working

Maybe specifying the worksheet would help:

with worksheets("sheet99").range("regionworkingdata")....

If that code is behind a worksheet, excel will assume that "regionworkingdata"
is a range on that sheet that owns the code.



Ray Batig wrote:
>
> Greetings,
>
> I wrote the following code to automatically resize a worksheet range that
> grows vertically.
>
> With Range("RegionWorkingData").Resize(1, 1)
> .Parent.Range(.Item(1), .Parent.Cells(Rows.Count, .Column) _
> .End(xlUp)).Name = "RegionWorkingData"
> End With
>
> I can't seem to get it to work. When I run it I get the error message that
> the Method 'Range of object'_Global' failer.
>
> Can some one help me fix this code?
>
> Thanks in advance for your help and guidance!!
>
> Ray

--

Dave Peterson

Thanks Dave,

I added the worksheet reference as you suggested, however, I get the same
error. This macro is in a module and is activated by a button on a
worksheet. Any more ideas?

Thanks again for your help!!

Ray


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:43FFB758.5343C128@verizonXSPAM.net...
> Maybe specifying the worksheet would help:
>
> with worksheets("sheet99").range("regionworkingdata")....
>
> If that code is behind a worksheet, excel will assume that
"regionworkingdata"
> is a range on that sheet that owns the code.
>
>
>
> Ray Batig wrote:
> >
> > Greetings,
> >
> > I wrote the following code to automatically resize a worksheet range
that
> > grows vertically.
> >
> > With Range("RegionWorkingData").Resize(1, 1)
> > .Parent.Range(.Item(1), .Parent.Cells(Rows.Count, .Column) _
> > .End(xlUp)).Name = "RegionWorkingData"
> > End With
> >
> > I can't seem to get it to work. When I run it I get the error message
that
> > the Method 'Range of object'_Global' failer.
> >
> > Can some one help me fix this code?
> >
> > Thanks in advance for your help and guidance!!
> >
> > Ray
>
> --
>
> Dave Peterson

Just the common suggestion: Check the spelling of the range name.

Your code worked ok for me. But I did need the qualifier when the code was
behind the worksheet.





Ray Batig wrote:
>
> Thanks Dave,
>
> I added the worksheet reference as you suggested, however, I get the same
> error. This macro is in a module and is activated by a button on a
> worksheet. Any more ideas?
>
> Thanks again for your help!!
>
> Ray
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:43FFB758.5343C128@verizonXSPAM.net...
> > Maybe specifying the worksheet would help:
> >
> > with worksheets("sheet99").range("regionworkingdata")....
> >
> > If that code is behind a worksheet, excel will assume that
> "regionworkingdata"
> > is a range on that sheet that owns the code.
> >
> >
> >
> > Ray Batig wrote:
> > >
> > > Greetings,
> > >
> > > I wrote the following code to automatically resize a worksheet range
> that
> > > grows vertically.
> > >
> > > With Range("RegionWorkingData").Resize(1, 1)
> > > .Parent.Range(.Item(1), .Parent.Cells(Rows.Count, .Column) _
> > > .End(xlUp)).Name = "RegionWorkingData"
> > > End With
> > >
> > > I can't seem to get it to work. When I run it I get the error message
> that
> > > the Method 'Range of object'_Global' failer.
> > >
> > > Can some one help me fix this code?
> > >
> > > Thanks in advance for your help and guidance!!
> > >
> > > Ray
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

One more thought (from a very similar post):

Are you running xl97 and calling the procedure from a control from the control
toolbox toolbar (placed on a worksheet)?

If that's the case, change the .takefocusonclick property to false for that
control.

Show the control toolbox toolbar.
click on the design mode icon
rightclick on the control
choose properties
change .takefocusonclick to false
click on the design mode icon (to get out of that mode)

Alternatively, if that control doesn't have that property, you could add this to
the top of your procedure:

Activecell.activate

(This bug was fixed in xl2k.)



Dave Peterson wrote:
>
> Just the common suggestion: Check the spelling of the range name.
>
> Your code worked ok for me. But I did need the qualifier when the code was
> behind the worksheet.
>
> Ray Batig wrote:
> >
> > Thanks Dave,
> >
> > I added the worksheet reference as you suggested, however, I get the same
> > error. This macro is in a module and is activated by a button on a
> > worksheet. Any more ideas?
> >
> > Thanks again for your help!!
> >
> > Ray
> >
> > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > news:43FFB758.5343C128@verizonXSPAM.net...
> > > Maybe specifying the worksheet would help:
> > >
> > > with worksheets("sheet99").range("regionworkingdata")....
> > >
> > > If that code is behind a worksheet, excel will assume that
> > "regionworkingdata"
> > > is a range on that sheet that owns the code.
> > >
> > >
> > >
> > > Ray Batig wrote:
> > > >
> > > > Greetings,
> > > >
> > > > I wrote the following code to automatically resize a worksheet range
> > that
> > > > grows vertically.
> > > >
> > > > With Range("RegionWorkingData").Resize(1, 1)
> > > > .Parent.Range(.Item(1), .Parent.Cells(Rows.Count, .Column) _
> > > > .End(xlUp)).Name = "RegionWorkingData"
> > > > End With
> > > >
> > > > I can't seem to get it to work. When I run it I get the error message
> > that
> > > > the Method 'Range of object'_Global' failer.
> > > >
> > > > Can some one help me fix this code?
> > > >
> > > > Thanks in advance for your help and guidance!!
> > > >
> > > > Ray
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson

--

Dave Peterson

Dave, that one fixed it. It was an old workbook that had been built in 97.
Looks like the characteristic hangs in there no matter what version you are
currently running.

Thanks for your help!

Ray

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:4400C20E.7CFB8A9E@verizonXSPAM.net...
> One more thought (from a very similar post):
>
> Are you running xl97 and calling the procedure from a control from the
control
> toolbox toolbar (placed on a worksheet)?
>
> If that's the case, change the .takefocusonclick property to false for
that
> control.
>
> Show the control toolbox toolbar.
> click on the design mode icon
> rightclick on the control
> choose properties
> change .takefocusonclick to false
> click on the design mode icon (to get out of that mode)
>
> Alternatively, if that control doesn't have that property, you could add
this to
> the top of your procedure:
>
> Activecell.activate
>
> (This bug was fixed in xl2k.)
>
>
>
> Dave Peterson wrote:
> >
> > Just the common suggestion: Check the spelling of the range name.
> >
> > Your code worked ok for me. But I did need the qualifier when the code
was
> > behind the worksheet.
> >
> > Ray Batig wrote:
> > >
> > > Thanks Dave,
> > >
> > > I added the worksheet reference as you suggested, however, I get the
same
> > > error. This macro is in a module and is activated by a button on a
> > > worksheet. Any more ideas?
> > >
> > > Thanks again for your help!!
> > >
> > > Ray
> > >
> > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > > news:43FFB758.5343C128@verizonXSPAM.net...
> > > > Maybe specifying the worksheet would help:
> > > >
> > > > with worksheets("sheet99").range("regionworkingdata")....
> > > >
> > > > If that code is behind a worksheet, excel will assume that
> > > "regionworkingdata"
> > > > is a range on that sheet that owns the code.
> > > >
> > > >
> > > >
> > > > Ray Batig wrote:
> > > > >
> > > > > Greetings,
> > > > >
> > > > > I wrote the following code to automatically resize a worksheet
range
> > > that
> > > > > grows vertically.
> > > > >
> > > > > With Range("RegionWorkingData").Resize(1, 1)
> > > > > .Parent.Range(.Item(1), .Parent.Cells(Rows.Count,
..Column) _
> > > > > .End(xlUp)).Name = "RegionWorkingData"
> > > > > End With
> > > > >
> > > > > I can't seem to get it to work. When I run it I get the error
message
> > > that
> > > > > the Method 'Range of object'_Global' failer.
> > > > >
> > > > > Can some one help me fix this code?
> > > > >
> > > > > Thanks in advance for your help and guidance!!
> > > > >
> > > > > Ray
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> >
> > --
> >
> > Dave Peterson
>
> --
>
> Dave Peterson