murfyang

04-01-2008, 07:33 AM

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.

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

However when i execute this, i keep getting "Application-defined or Object defined error".

Thanks for any help rendered. Murf fm Singapore

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

However when i execute this, i keep getting "Application-defined or Object defined error".

Thanks for any help rendered. Murf fm Singapore