PDA

View Full Version : Determine last used cell of a column and put formula 3 rows beneath it



Riaaz66
02-09-2012, 05:43 AM
Hi,

Can anyone help me with the following:

I have a worksheet where I want to put a sum formula of a specified column.
The formula have to be in a cell which is three rows beneath the last determined cell of a certain column.
Now, I wrote the macro to determine the last cell of a certain column, but how do I put the sum formula. Please note, that the data in the columns, changes every day in lenght.

For example (see attachement):
Column A has 42 rows with numbers. I want to sum that column in cell A46. The formula in A46 should be like SUM(A2:"[last used cell]"

Hope someone can help me.

Thanks in advance.

Sub Test()
Dim LastRow As Long

Sheets("Sheet1").Activate
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

End With
End Sub

Bob Phillips
02-09-2012, 05:51 AM
Sub Test()
Dim LastRow As Long

With Sheets("Sheet1")

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Cells(LastRow + 4, "A").Formula = "=SUM(A2:A" & LastRow & ")"
End With
End Sub

Riaaz66
02-09-2012, 06:40 AM
Thanks. It worked.





Sub Test()
Dim LastRow As Long

With Sheets("Sheet1")

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Cells(LastRow + 4, "A").Formula = "=SUM(A2:A" & LastRow & ")"
End With
End Sub