Macro to add count forumla with dynamic range

[falloutx]

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


A small example is

Code:
--------------------
Range("P7").Select
dynamicRow = 8 'may change dynamically
fixedStartRow = 5 - Row
ActiveCell.FormulaR1C1 = "=SUM("+fixedStartRow+":R[-1]C)"
--------------------


But the code above does not work!


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

Code:
--------------------
Range("P7").Select 'Selected cell may change dynamically
ActiveCell.FormulaR1C1 = "=SUM(P5:R[-1]C)"
--------------------


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

=SUM('P5'6)

Why does it have 'P5' insteadd of just P5


Please advise! thanks!

 

[jindon]

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 reserve
word

 

[falloutx]

thanks!!!!