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':P6)

Why does it have 'P5' insteadd of just P5


Please advise! thanks!


--
falloutx
------------------------------------------------------------------------
falloutx's Profile: http://www.excelforum.com/member.php...o&userid=30560
View this thread: http://www.excelforum.com/showthread...hreadid=551270

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

--
jindo
-----------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php...fo&userid=1313
View this thread: http://www.excelforum.com/showthread.php?threadid=55127

thanks!!!!


--
falloutx
------------------------------------------------------------------------
falloutx's Profile: http://www.excelforum.com/member.php...o&userid=30560
View this thread: http://www.excelforum.com/showthread...hreadid=551270