I have a named range "storecntr" that gives me the number of stores on a
worksheet. A formula is entered into cell A2 of another sheet that needs to
be copied down for all the stores. I want to use storecntr to tell how many
rows to copy the formula down, but as usual I'm having trouble because I
swear I have no logic. Any help would be appreciated. Thanks.

Hi,
Try this:

Range("a2").Resize(Range("storecntr"), 1).Formula = Range("a2").Formula

HTH

"cottage6" wrote:

I have a named range "storecntr" that gives me the number of stores on a
worksheet. A formula is entered into cell A2 of another sheet that needs to
be copied down for all the stores. I want to use storecntr to tell how many
rows to copy the formula down, but as usual I'm having trouble because I
swear I have no logic. Any help would be appreciated. Thanks.

Toppers, thanks for the response. I'm getting a Method 'Range of object
_Global' failed error. Any ideas?

"Toppers" wrote:

Hi,
Try this:

Range("a2").Resize(Range("storecntr"), 1).Formula = Range("a2").Formula

HTH

"cottage6" wrote:

I have a named range "storecntr" that gives me the number of stores on a
worksheet. A formula is entered into cell A2 of another sheet that needs to
be copied down for all the stores. I want to use storecntr to tell how many
rows to copy the formula down, but as usual I'm having trouble because I
swear I have no logic. Any help would be appreciated. Thanks.

Hi,
You will get this message if your named range is not defined or is
mis-spelled.
Otherwise ... no idea!

"cottage6" wrote:

Toppers, thanks for the response. I'm getting a Method 'Range of object
_Global' failed error. Any ideas?

"Toppers" wrote:

Hi,
Try this:

Range("a2").Resize(Range("storecntr"), 1).Formula = Range("a2").Formula

HTH

"cottage6" wrote:

I have a named range "storecntr" that gives me the number of stores on a
worksheet. A formula is entered into cell A2 of another sheet that needs to
be copied down for all the stores. I want to use storecntr to tell how many
rows to copy the formula down, but as usual I'm having trouble because I
swear I have no logic. Any help would be appreciated. Thanks.

I can't see the whole history of this thread, but just to add another
possibility which may or may not be applicable.

It could depend on where the code is located. If the code is in a sheet
module of sheet1 and storecntr is a workbook level range that references a
range in Sheet2, you will get that error. In that case you would need


Range("a2").Resize(Worksheets("Sheet2").Range( _
"storecntr"), 1).Formula = Range("a2").Formula

as an example. (Range("A2") is assumed to be on Sheet1 - same sheet as the
code)

--
Regards,
Tom Ogilvy


"Toppers" wrote in message
...
Hi,
You will get this message if your named range is not defined or
is
mis-spelled.
Otherwise ... no idea!

"cottage6" wrote:

Toppers, thanks for the response. I'm getting a Method 'Range of object
_Global' failed error. Any ideas?

"Toppers" wrote:

Hi,
Try this:

Range("a2").Resize(Range("storecntr"), 1).Formula =
Range("a2").Formula

HTH

"cottage6" wrote:

I have a named range "storecntr" that gives me the number of stores
on a
worksheet. A formula is entered into cell A2 of another sheet that
needs to
be copied down for all the stores. I want to use storecntr to tell
how many
rows to copy the formula down, but as usual I'm having trouble
because I
swear I have no logic. Any help would be appreciated. Thanks.

Thanks Tom.

I put my code in a general module rather than sheet module. When I tried my
code in a worksheet module I got the error message "Application-defined or
Object-defined error" which is not the error reported; however, your code
removed my error. (The named range was on Sheet2).

Is the lesson that I should always fully qualify variables?

Thanks again.


"Tom Ogilvy" wrote:

I can't see the whole history of this thread, but just to add another
possibility which may or may not be applicable.

It could depend on where the code is located. If the code is in a sheet
module of sheet1 and storecntr is a workbook level range that references a
range in Sheet2, you will get that error. In that case you would need


Range("a2").Resize(Worksheets("Sheet2").Range( _
"storecntr"), 1).Formula = Range("a2").Formula

as an example. (Range("A2") is assumed to be on Sheet1 - same sheet as the
code)

--
Regards,
Tom Ogilvy


"Toppers" wrote in message
...
Hi,
You will get this message if your named range is not defined or
is
mis-spelled.
Otherwise ... no idea!

"cottage6" wrote:

Toppers, thanks for the response. I'm getting a Method 'Range of object
_Global' failed error. Any ideas?

"Toppers" wrote:

Hi,
Try this:

Range("a2").Resize(Range("storecntr"), 1).Formula =
Range("a2").Formula

HTH

"cottage6" wrote:

I have a named range "storecntr" that gives me the number of stores
on a
worksheet. A formula is entered into cell A2 of another sheet that
needs to
be copied down for all the stores. I want to use storecntr to tell
how many
rows to copy the formula down, but as usual I'm having trouble
because I
swear I have no logic. Any help would be appreciated. Thanks.