Help

[GaryHJones]

I have been trying to find a solution to my problem, but cant seem to find one.

i have a spreadsheet with names in column A, and events in column B and dates in column C.

the format of the sheet, is like this

Column A Column B Column C

Fred Bloggs
	Male 50 Freestyle	09/03/2019
	1
	Male 100 Freestyle	31/03/2019
	1
	Male 200 Freestyle	01/02/2019
	1
	Male 400 Freestyle	21/09/2019
	1
	Male 800 Freestyle	13/09/2019
	1
	Male 50 Breaststroke	22/09/2019
	1
	Male 100 Breaststroke	01/02/2019
	1
	Male 200 Breaststroke	02/02/2019
	1
	Male 50 Butterfly	21/09/2019
	1
	Male 100 Butterfly	07/07/2019
	1
	Male 200 Butterfly	03/02/2019
	1
	Male 50 Backstroke	22/09/2019
	1
	Male 100 Backstroke	22/09/2019
	1
	Male 200 Backstroke	21/09/2019
	1
	Male 100 Individual Medley	09/03/2019
	1
	Male 200 Individual Medley	22/09/2019
Bill Bob	1
	Male 50 Freestyle	16/06/2019
	1
	Male 100 Freestyle	23/03/2019
	1
	Male 50 Breaststroke	16/06/2019
	1
	Male 50 Butterfly	16/06/2019
	1
	Male 50 Backstroke	16/06/2019
	1
	Male 100 Individual Medley	16/06/2019

<colgroup><col width="95" style="width: 71pt;"><col width="173" style="width: 130pt;"><col width="93" style="width: 70pt;"></colgroup><tbody>
</tbody>

i have a another sheet where i want to be able to select a name from a validated List of Names , and return the correct dates for each of the events listed.

i don't want to have to copy the names down, against the events as there are 100s of names and events.
thanks for any help anyone can give.

 

[JLGWhiz]

This assumes you are using a validation drop down list. You did not specify where on "another sheet" you have the list but the code below uses cell A1 of Sheet 2 to illustate the method. You can change the code to reflect the correct cell for the drop down list.

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, fn As Range, rng As Range, cel As Range
Set sh1 = Sheets(1)
Set sh2 = Sheets(2)
lr = sh1.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    If sh2.Range("A1") <> "" Then
        Set fn = sh1.Range("A:A").Find(sh2.Range("A1").Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                If fn.End(xlDown).Row > lr Then
                    Set cel = sh1.Cells(lr, 3)
                Else
                    Set cel = fn.End(xlDown).Offset(-1, 2)
                End If
                Set rng = sh1.Range(fn.Offset(2, 1), cel)
            End If
        rng.Copy sh2.Range("B3")
    End If
End Sub

 

[GaryHJones]

JLGWhiz
thanks for your help, that worked a treat.

Gary

 

[JLGWhiz]

JLGWhiz
thanks for your help, that worked a treat.

Gary

You are welcome,
Regards, JLG