Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Active cell
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Active cell
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 -- Gary''s Student - gsnu200908 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Active cell
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! "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200908 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Active cell
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 -- Rick (MVP - Excel) "puiuluipui" wrote in message ... 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! "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200908 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Active cell
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) "puiuluipui" wrote: 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! "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200908 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Active Cell? | Excel Discussion (Misc queries) | |||
set the background color of the current cell(active cell) | New Users to Excel | |||
referring to formula in a non active cell from active cell | Excel Discussion (Misc queries) | |||
value of the active cell | Excel Worksheet Functions | |||
HOW TO COPY 480 ACTIVE E-MAIL ADDRESSES CLM "G" ON AN ACTIVE EXCE. | Excel Discussion (Misc queries) |