Macro to add count forumla with dynamic range

I am trying to use macro to add a COUNT() formula with a dynamic range.

A small example is

Please Login or Register  to view this content.

But the code above does not work!


Then I thought I can work ard iby making the ActiveCell dynamic.

Please Login or Register  to view this content.

But this code produce the following in the cell box in excel sheet.

=SUM('P5':P6)

Why does it have 'P5' insteadd of just P5


Please advise! thanks!

Hi

try

"=sum(r5c:r[-1]c)"

or

"=sum(r" & fixedStartRow & "c:r" & dynamicRow & "c)"

Note: Not sure if the line "fixedStartRow = 5 - Row" is working...
If Row is a variable, change it to other name due to the vba reserved word.

thanks!!!!