VBA- Finding Highest Value and Matching to Names in other column.

[ddaavidd_david]

I have a question which says: "Refer to the "Final Marks" column (Column N) and find the student with the highest mark. Display the student name (from Column D) into the cell Q2. Answer should be in the form of VBA codes."

I have managed to do this using Excel function =INDEX(D2:D296,MATCH(MAX(N2:N296),N2:N296,0))
How do I convert that into VBA codes?

 

[Tim_Excel_]

Please use a larger font for readability.

Sub transferdata()
With Workbooks("REFERENCE").Sheets("REFERENCE")

Set FindHighesMark = .Range("N10:N50").Find(Application.WorksheetFunction.Max(Columns("M")), lookat:=xlWhole, MatchCase:=False).Row

.Range("Q2:Q5").Value = .Cells(FindHighestMark, "D")

End With
End Sub

But, I don't think it's fair that you let us do your homework

 

[steve the fish]

Hi. You should really do your own homework. You dont gain much from being fed an answer. Id use a combination of max, match and offset for that i think but not in the form of a worksheet formula.