Active cell

[puiuluipui]

Hi, i have this code:
Sub back()

Columns("I:I").Select
Selection.Find(What:="Total", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(1, 0).Select
End Sub
All i need is to sum all cells with data below active cell.
Can this be done?
Thanks!

 

[Gary''s Student]

Sub dural()
Dim r As Range
Set r = Range(ActiveCell.Offset(1, 0), Cells(Rows.Count, ActiveCell.Column))
x = Application.WorksheetFunction.Sum(r)
MsgBox x
End Sub

 

[puiuluipui]

Hi Gary, it's working if i manually select last cell in "I" column that
contain word "Total".
The problem is that i dont need msgbox with the result. I need the result in
a cell. My code finds last cell in "I" column that contain "Total" and then
select next cell. I just need to sum cells below "Total" (last "total" in
range) and the result to be in a cell. I need the result to remain in my
table.
Can this be done?
Thanks!

 

[Rick Rothstein]

Give this code a try...

Sub Back()
Dim LastCell As Range
Set LastCell = Columns("I").Find(What:="Total", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False, SearchFormat:=False)
LastCell.Offset(1, 0).Value = Application.WorksheetFunction. _
Sum(Range(ActiveCell, LastCell))
End Sub

 

[JLatham]

Just change his
MsgBox x
statement to become
ActiveCell = x

or skip a step and change
x = Application.WorksheetFunction.Sum(r)
MsgBox x

to become simply
ActiveCell = Application.WorksheetFunction.Sum(r)