Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing top 8 marks + display
I need some advice/help. 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 Anybody have nay adice/help on parts or all of it? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing top 8 marks + display
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 Next i set rng = Range(Cells(14,2),cells(14,lc)) rng.Formula = "=Sum(B2:B9)" rng.offset(1,0).Value = 80 rng.offset(2,0).Formula = "=B14/B15" End Sub -- Regards, Tom Ogilvy "Jeff Garrett" wrote in message ... I need some advice/help. 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 Anybody have nay adice/help on parts or all of it? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
to get the relative marks from a table of names,subjects & marks? | Excel Worksheet Functions | |||
Quotation Marks | Excel Discussion (Misc queries) | |||
Quote Marks Through VBA | Excel Discussion (Misc queries) | |||
check marks | New Users to Excel | |||
Use quotation marks in a formula to display a qty in feet and inc. | Excel Worksheet Functions |