VBA problems with finding max values en copying names to a different worksheet.

[TimExcel123]

Hi guys,

My name is Tim and I am new to this Forum.
I experience a lot of trouble while designing my first code in Excel VBA. The problem is as following: in column B(worksheet 1), I inserted the names of some students. in column C, I inserted the marks of the corresponding students (see table 1).

In worksheet 2 table 2, I want cells A1:A3 to display the names of the 3 best students. After watching a lot of Youtube-tutorials, I still did not manage tot figure out how to do it. Is there anyone who could help me designing this code in VBA?

Thanks in advance!
-------------------------------------------------------

Table 1 worksheet 1:

Student	Mark
Alex	9,5
Sophie	7
Nick	8
Paul	4
Helena	9
jan	6

<tbody>
</tbody>

table 2 worksheet 2:

Best students

?

?

?

<tbody>
</tbody>

 

[DanteAmor]

Using the Large formula you can find the first 3 places.

Sub finding_max_values()
    Dim i As Long, r As Long
    r = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
    For[COLOR=#0000ff] i [/COLOR]= 1 To 3
        Sheets("Sheet2").Range("A" & i + 1).Value = Evaluate _
            ("=INDEX(Sheet1!B1:B" & r & ",SUMPRODUCT((LARGE(Sheet1!C2:C" & r & "+(ROW(C2:C" & r & ")/1000)," &[COLOR=#0000ff] i [/COLOR]& _
            ")=Sheet1!C2:C" & r & "+(ROW(C2:C" & r & ")/1000))*(ROW(C2:C" & r & "))))")
    Next
End Sub

---
Or copying the data from sheet1 to sheet2, sorting and deleting data from 4 down, that way only the first 3 are left.

Sub Macro6()
    Sheets("Sheet1").Range("B:C").Copy Sheets("Sheet2").Range("A1")
    Sheets("Sheet2").Range("A1").CurrentRegion.Sort key1:=Sheets("Sheet2").Range("B1"), order1:=xlDescending, Header:=xlYes
    Sheets("Sheet2").Range("A5:B" & Rows.Count).ClearContents
End Sub

 

[TimExcel123]

Dear Dante Amor,

Thanks so much for helping me!
Your answer is really helpful!

Regards, Tim

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.