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?

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?