Results 1 to 3 of 3

020904, 18:53 #1Registered User
 Join Date
 Oct 2003
 Posts
 311
Unanswered: I want to sum from 1 row above current cell to cell 7 ?
I want to sum from 1 row above current cell to cell 7
say i am in col b row 55
i want my formula to be "=Sum(b54:b7)"
i have used things like "=sum(b" & cntr & ":b7)"
where i have counted rows, but this seems rediculous (worse then my spelling!)
can i get the row # of the active cell or something like that?
M~

020904, 22:15 #2Registered User
 Join Date
 Jan 2002
 Location
 Bay Area
 Posts
 513
This Excel solution may help. A function can be used to sum rows above the current cell. One thing I don't understand is that you have to reenter the formula if you change values in the cells above.
In the cell where you want the total, type "=sumtorow7()", without quotes. The function skips over cells that are empty. I have attached an Excel file to show this function working.
Can anyone make this solution perfect, or is it just a fact of life that you need to refresh a function formula each time you change the data?
Function SumToRow7()
'sums only cells above that have values, up to row 7
Dim i, BottomRow, TopRow As Integer
TopRow = 7
BottomRow = ActiveCell.Row  1
For i = TopRow To BottomRow
If Trim(Cells(i, ActiveCell.Column).Value) <> "" Then
total = total + Cells(i, ActiveCell.Column).Value
End If
Next
SumToRow7 = total
End Function

021004, 10:25 #3Registered User
 Join Date
 Oct 2003
 Posts
 1,091
Try this:
Code:Sub MakeTotalRows() Dim myTotals As Range Dim myRange As Range Dim myRows As Integer Dim myColumns As Integer Dim i As Integer ' Set range Set myRange = ActiveCell.CurrentRegion ' Count number of rows & columns myRows = myRange.Rows.Count myColumns = myRange.Columns.Count ' Start at top, go to first column in last row myRange.Range("A1").Select ActiveCell.Offset(myRows  1, 0).Select Application.ScreenUpdating = False ' Cycle through columns, entering Sum, Total, coloring ' Checks each column for numeric value, if so, places ' SUM function of that column in the cell; ' If not then places word "Total" in cell. For i = 0 To myColumns  1 If IsNumeric(ActiveCell.Offset(0, i).Value) Then ActiveCell.Offset(1, i).FormulaR1C1 = "=SUM(R[" & myRows  1 & "]C:R[1]C)" ActiveCell.Offset(1, i).Font.Bold = True ActiveCell.Offset(1, i).Font.ColorIndex = 55 ActiveCell.Offset(1, i).Borders.Weight = xlMedium ActiveCell.Offset(1, i).Borders.ColorIndex = 55 ActiveCell.Offset(1, i).Interior.ColorIndex = 36 Else ActiveCell.Offset(1, i).Value = "Total" ActiveCell.Offset(1, i).Font.ColorIndex = 55 ActiveCell.Offset(1, i).Font.Bold = True ActiveCell.Offset(1, i).Borders.Weight = xlMedium ActiveCell.Offset(1, i).Borders.ColorIndex = 55 ActiveCell.Offset(1, i).Interior.ColorIndex = 36 End If Next i Application.ScreenUpdating = True End Sub
old, slow, and confused
but at least I'm inconsistent!
Rich
(retired Excel 2003 user, 3/28/2008)
How to ask a question on forums