Formula in macro
Posted by sean tobin on July 27, 2001 4:45 PM
I've got a macro that needs to sum a varying amount of rows. By "varying" I mean, the macro is used once a month but each month there are a different number of rows. I need the formula to be in the cell below the last row of data. I.e., this month there are, say, 400 rows. The formula in this case needs to sum D1:D400 and place the result in D401. Next month there might be 300 rows. How do I get the formula to sum just the rows that are there. It keeps wanting to hard-code the cells to be summed.
Posted by Ivan F Moala on July 28, 2001 3:25 AM
Try this.......
Sub SumFormula_InD()
Dim LRw As Double
'Cells(65536,4) = Range("D65536") ie. last Row in coloun D
LRw = Cells(65536, 4).End(xlUp).Row
'Put Sum formula into the last Blank Row in D column
Cells(LRw + 1, 4) = "=SUM(R[-" & LRw & "]C:R[-1]C)"
End Sub
Ivan
