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:

[TABLE="width: 100"]
<tbody>[TR]
[TD]Student[/TD]
[TD]Mark[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alex
[/TD]
[TD]9,5[/TD]
[/TR]
[TR]
[TD]Sophie[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Nick[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Helena[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]jan[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

table 2 worksheet 2:
[TABLE="width: 100"]
<tbody>[TR]
[TD]Best students[/TD]
[/TR]
[TR]
[TD]?[/TD]
[/TR]
[TR]
[TD]?[/TD]
[/TR]
[TR]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.