sorting a list

[striker13]

I'm using VBA in Excel.

I have a list of students first and last names in A and B column, and grades in columns C to K. And i need to find the average of all of these students in column L. After i found the averages, i need to find the highest average grade and the students name who has this highest grade. if theres 2 or more with the same highest grade, then i need to find them all. the result should be displayed in an message box or in lets say cell 1N.

Many many thanks in advance!

[code green]

I'm using VBA in Excel

Where is your VBA code?

[striker13]

What you mean where?

anyway, i managed to do some myself, but when it shows the highest average grade in messagebox, it does not use any commas " , " in numbers, so for example, average grade is 4,3 it shows as 43. And i also can't figure out, how to make it show the student's name.

I uploaded my excel file, so you can have a look at it:

http://www.upload.ee/files/574980/hinded.rar.html

[code green]

I mean post your VBA code and define the exact problem

[striker13]

Sub keskmine3()
Dim m As Long, n As Long, c As Range
    
    Range("l2").Activate
    
    ActiveCell.FormulaR1C1 = _
        "=(RC[-9]+RC[-8]+RC[-7]+RC[-6]+RC[-5]+RC[-4]+RC[-3]+RC[-2]+RC[-1])/9"
    Range("L2").Select
    Selection.AutoFill Destination:=Range("L2:L33"), Type:=xlFillDefault
 Columns("L:L").Select
    Selection.NumberFormat = "0.0"
For Each c In Range("L2:L" & Range("L" & Rows.Count).End(xlUp).Row)
    n = ExtractNumber(c.Text)
    If m < n Then m = n
Next
MsgBox "Suurim keskmine hinne on: " & m
    
End Sub

Function ExtractNumber(r As String) As Long
With CreateObject("vbscript.regexp")
    .Pattern = "\D"
    .Global = True
    ExtractNumber = Val(.Replace(r, ""))
End With
End Function

anyway, i managed to do some myself, but when it shows the highest average grade in messagebox, it does not use any commas " , " in numbers, so for example, average grade is 4,3 it shows as 43. And i also can't figure out, how to make it show the student's name. And if two or more students have the same highest average grade, how to make it show both of their names.

[Guido Geurs]

dear,

I hope this macro will help you =

Sub FindBest()
Dim OFFSETidx As Integer
Dim TOTAL As Integer
Dim MSGTEXT As String
   Range("A2").Activate
'§ calculate the average for each line
      Do While ActiveCell.Value <> ""
         TOTAL = 0
         For OFFSETidx = 2 To 10
            TOTAL = TOTAL + ActiveCell.Offset(0, OFFSETidx).Value
         Next
         ActiveCell.Offset(0, 11).Value = TOTAL / 9
         ActiveCell.Offset(1, 0).Activate
      Loop
'§ set number format
   Columns("L:L").Select
   Selection.NumberFormat = "0.0"
'§ sort
   Range("A1:L33").Sort Key1:=Range("L2"), Order1:=xlDescending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
'§ find the best
   Range("A2").Activate
      MSGTEXT = "The best are=" & vbCrLf
      MSGTEXT = ActiveCell.Value & " " & ActiveCell.Offset(0, 1).Value & _
            " average = " & Format(ActiveCell.Offset(0, 11).Value, "#.#") & vbCrLf
      ActiveCell.Offset(1, 0).Activate
      Do While ActiveCell.Offset(0, 11).Value = ActiveCell.Offset(-1, 11).Value
         MSGTEXT = MSGTEXT & ActiveCell.Value & " " & ActiveCell.Offset(0, 1).Value & _
               " average = " & Format(ActiveCell.Offset(0, 11).Value, "#.#") & vbCrLf
         ActiveCell.Offset(1, 0).Activate
      Loop
'§ send msgbox
   MsgBox (MSGTEXT)
End Sub

br,

[striker13]

Thousands and more thanks :D