R/T error 1004 - But WHY?
 

ActiveCell.Formula = "=Counta(Selection.Offset(-1).end(xlUp) & "":"" &
Selection.Offset(-1))" 'Top to bottom

R/T error 1004 - But WHY?
 

Replace:

"=Counta(Selection.Offset(-1).end(xlUp) & "":"" & Selection.Offset(-1))"

with

"=Counta(" & Selection.Offset(-1).end(xlUp).address & ":" &
Selection.Offset(-1).address & ")"

Sam

"JMay" wrote:

ActiveCell.Formula = "=Counta(Selection.Offset(-1).end(xlUp) & "":"" &
Selection.Offset(-1))" 'Top to bottom

R/T error 1004 - But WHY?
 

That formula is not valid... Everything is inside the quotes so your formula
is exactly what is typed. Additionally the default return value of a range is
the value and not the address. You want something more like...

ActiveCell.Formula = "=Counta(" & Selection.Offset(-1).end(xlUp).address &
":" &
Selection.Offset(-1).address & ")" 'Top to bottom

--
HTH...

Jim Thomlinson


"JMay" wrote:

ActiveCell.Formula = "=Counta(Selection.Offset(-1).end(xlUp) & "":"" &
Selection.Offset(-1))" 'Top to bottom

R/T error 1004 - But WHY?
 

Jim,
Thank you so much - 1) for your fine/understandable explanation and 2)
the revised formula - WORKS GREAT!!
Jim

"Jim Thomlinson" wrote:

That formula is not valid... Everything is inside the quotes so your formula
is exactly what is typed. Additionally the default return value of a range is
the value and not the address. You want something more like...

ActiveCell.Formula = "=Counta(" & Selection.Offset(-1).end(xlUp).address &
":" &
Selection.Offset(-1).address & ")" 'Top to bottom

--
HTH...

Jim Thomlinson


"JMay" wrote:

ActiveCell.Formula = "=Counta(Selection.Offset(-1).end(xlUp) & "":"" &
Selection.Offset(-1))" 'Top to bottom

R/T error 1004 - But WHY?
 

This may not fit, but if you're trying to count the number of cells filled
between row 1 and the row above, you could use:

activecell.formular1c1 = "=counta(r1c:r[-1]c)")

r1c is row 1 of the same column
r[-1]c is the row above of the same column

If you had headers in rows 1:2 that you wanted to avoid:
activecell.formular1c1 = "=counta(r3c:r[-1]c)")



JMay wrote:

ActiveCell.Formula = "=Counta(Selection.Offset(-1).end(xlUp) & "":"" &
Selection.Offset(-1))" 'Top to bottom

--

Dave Peterson