Determine last used cell of a column and put formula 3 rows beneath it

[Riaaz66]

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.

[vba]Sub Test()
Dim LastRow As Long

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

End With
End Sub[/vba]

[Bob Phillips]

[vba]


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
[/vba]

[Riaaz66]

Thanks. It worked.

[vba]


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
[/vba]