Use counter to copy formulas
 

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.

Use counter to copy formulas
 

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.

Use counter to copy formulas
 

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.

Use counter to copy formulas
 

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.

Use counter to copy formulas
 

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.

Use counter to copy formulas
 

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.

Use counter to copy formulas
 

I doubt it would be detrimental, but I am not sure I would say that is a
practice I champion. The language supports the use of the short cut methods
we all are so fond of using, so no reason to change. Just be cognizant of
limitations and adjust accordingly.

--
Regards,
Tom Ogilvy


"Toppers" wrote in message
...
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.