Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing rows
I want to sum the integer elements from row 2 to row 9 inclusive, then
display it in a cell a couple rows below the last element, say row 14. The trick though, is I am iterating through each column in the worksheet. Sub SortEachColumn() Range("A14") = "Best 8 Marks" Range("A15") = "Total Possible" Range("A16") = "Total Mark" lc = cells(2, "iv").End(xlToLeft).Column For i = 2 To lc lr = cells(Rows.Count, i).End(xlUp).Row Next i End Sub Notice in line 6, where it goes to the end (End(xlUp)), that is too far. I only want from row 2 to row 9 inclusive, and then display the sum in row 14 of that column |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing rows
It appears it is always off one. Will it work to add this line below:
lr = cells(Rows.Count, i).End(xlUp).Row lr = lr -1 (Added line) It is a little difficult to see what you are trying to achieve, but I think it is a set of sub totals below the data. "Jeff Garrett" wrote: I want to sum the integer elements from row 2 to row 9 inclusive, then display it in a cell a couple rows below the last element, say row 14. The trick though, is I am iterating through each column in the worksheet. Sub SortEachColumn() Range("A14") = "Best 8 Marks" Range("A15") = "Total Possible" Range("A16") = "Total Mark" lc = cells(2, "iv").End(xlToLeft).Column For i = 2 To lc lr = cells(Rows.Count, i).End(xlUp).Row Next i End Sub Notice in line 6, where it goes to the end (End(xlUp)), that is too far. I only want from row 2 to row 9 inclusive, and then display the sum in row 14 of that column |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing rows
I don't think that is what I am looking for.
I have 200+ columns of data and each column has data in rows 1-12 inclusive. I want to go through each column (starting from column B), and go to the end of the worksheet (coumn IV). While in each column, I sort the data descending. I already have the function to sort descending and the for loop to go over each column. The problem for me is I can't figure out how to do the next part. On cell (row) 14 of each column, I want to sum the elements from row 2 to row 9 inlcusive, and put that sum in cell 14 of that column. In (row) cell 15, I want to put 80 in the cell of each column. In (row) cell 16, I want to divide cell 14 but cell 15, and put that in cell 16 of that column. Here is what I have so far: Sub SortEachColumn() Range("A14") = "Best 8 Marks" Range("A15") = "Total Possible" Range("A16") = "Total Mark" lc = Cells(1, "iv").End(xlToLeft).Column For i = 2 To lc 'column 2 to the last column lr = Cells(Rows.Count, i).End(xlUp).Row 'sorts the column descending Range(Cells(2, i), Cells(lr, i)).Sort key1:=Cells(2, i), Order1:=xlDescending 'This is where I am stuck and need someone's help Next i End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
summing only certain rows | Excel Discussion (Misc queries) | |||
Summing column and rows | Excel Discussion (Misc queries) | |||
Summing Alternate Rows | Excel Discussion (Misc queries) | |||
Summing all rows with certain specification | Excel Worksheet Functions | |||
Summing rows | Excel Programming |