Solved: Sum Dynamic row in VBA

[murfyang]

Hi have being trying to search the threads but unable to find any close to what i am trying to do. Am still trying to improve on VBA.

[VBA]
Sub sum_rows()
Dim no_of_rows as variant

no_of_rows=range("a5") 'A5 contains the value of nos of rows of data to sum. This value is variable. eg if A5=12

Range("G20").select 'this the cell selected to input the Sum formula
ActiveCell.FormulaR1C1 = "=SUM(r[-no_of_rows]C:R[-1]C)"

'The above formula is supposed to sum the nos of rows up from G8 to G19 since A5=12

end sub
[/VBA]
However when i execute this, i keep getting "Application-defined or Object defined error".
Thanks for any help rendered. Murf fm Singapore

[Bob Phillips]

[vba]

Sub sum_rows()
Dim no_of_rows As Variant

no_of_rows = Range("A5").Value
Range("G20").FormulaR1C1 = "=SUM(r[-" & no_of_rows & "]C:R[-1]C)"

End Sub
[/vba]

[murfyang]

wow, thanks thanks thanks, solve!
just wondering where do you find when to add in the ampersand symbol?

[Bob Phillips]

It's obvious if you know the rules. A string is set of characters between two quotes. If you want to include a variable value you have to close the string and concatenate (&) the variable value then reopen the string.