Run-time error '1004': Application defined or Object defined error
 

I am having difficulty with a run-time error '1004'.

Excel does not like it when I have substituted a variable
(linekount_I) for a relative cell address within a counta formula.

This generates the error:

ActiveCell.FormulaR1C1 = "=COUNTA(R[-linekount_I]C:R[-1]C) & ""
Invoice(s)"""

This works fine:

ActiveCell.FormulaR1C1 = "=COUNTA(R[-5]C:R[-1]C) & "" Invoice
(s)"""

I need to have the formula created with a varying number of rows. The
hard coded 5 will not suffice.

Any help would be appreciated

Run-time error '1004': Application defined or Object defined error
 

The below code should work.

ActiveCell.FormulaR1C1 = "=COUNTA(R[-" & linekount_I & "]C:R[-1]C) & ""
Invoice(s)"""

If this post is helpful click Yes
---------------
Jacob Skaria

Run-time error '1004': Application defined or Object defined error
 

from
ActiveCell.FormulaR1C1 = "=COUNTA(R[-linekount_I]C:R[-1]C) & ""

to
ActiveCell.FormulaR1C1 = "=COUNTA(R" & (-1 * linekount_I) & "C:R[-1]C)"


" wrote:

I am having difficulty with a run-time error '1004'.

Excel does not like it when I have substituted a variable
(linekount_I) for a relative cell address within a counta formula.

This generates the error:

ActiveCell.FormulaR1C1 = "=COUNTA(R[-linekount_I]C:R[-1]C) & ""
Invoice(s)"""

This works fine:

ActiveCell.FormulaR1C1 = "=COUNTA(R[-5]C:R[-1]C) & "" Invoice
(s)"""

I need to have the formula created with a varying number of rows. The
hard coded 5 will not suffice.

Any help would be appreciated