Trying to Find what function or code to use to auto populate certain data

[Blusers480]

I am trying to find the correct function or code to use when looking at certain match parameters as I have mutiple graphs and data sets where I need to populate data and the team doesnt always have the same players. I constnatly need to sort the data based on different paramters however this then changes the numbers for the athletes in different graphs. For example in the photo attached I want a function that states when the athletes name is "Harry" and I want to populate his total distance data into a different graph. So in simple terms I want a function that says, when athletes name is "Harry" grab data from same row and designated column (e.g. total distance)

 

[RobP]

Hi if I assume Column G is empty (and "Name" would be in Col B, we will use cell "G1" for you to type your search name (eg. Harry in this case.)

Sub test()

Dim intlastrow As long
Dim Rng as Range

intlastrow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row 'find last row in Names col (assuming Names are in Col B. = 2

Set Rng = Range("B2", "B" & intlastrow).Find(What:=Range("G1").Value, _ 'define Rng as your column of Names, and the cell "G1" as your search name
    LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

If Rng Is Nothing Then Exit Sub  'exit if no match found

MsgBox (Rng.Offset(0, 1)) ' display a message box which shows the value from the matching Row number, but Offset by (0 Rows, 1 Column) = whats in the cell to the right of my result.


End Sub

Let me know how you get on with that.
cheers
Rob