| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
R/T error 1004 - But WHY?
ActiveCell.Formula = "=Counta(Selection.Offset(-1).end(xlUp) & "":"" &
Selection.Offset(-1))" 'Top to bottom |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| run time error 1004 general odbc error excel 2003 vba | Excel Programming | |||
| runtime error '1004' application or object defined error | Excel Programming | |||
| Error when cell A1 is not active and xlInsideVertical border formatthrowing error 1004 | Excel Programming | |||
| Error handling error # 1004 Run-time error | Excel Programming | |||
| run-time error '1004': Application-defined or object-deifined error | Excel Programming | |||
Linear Mode
