Creating A List When a Value Falls Within A Range

[Ark68]

I am here hoping someone can help me with coding this particular task.

I have a service time, as a decimal, assigned to variable 't_min'. In my example , t_min = 0.72 (5:15 PM)

I have a staff schedule in workbook("wb"), worksheet("STAFF"). This object is assigned to variable ws_corestaff.

Excel 2010
	A	B	C	D	E
3	Staff Roster
4	CUE1	1	Kelly	0.291667	0.625
5	CUE2	X	Not Staffed	0.291667	0.625
6	CUL1	2	Jen	0.666667	1
7	HPE1	A	Morgan	0.291667	0.625
8	HPE2	X	Not Staffed	0.291667	0.625
9	HPL1	C	Tessa	0.5625	0.895833
10	HPL2	X	Not Staffed
11	RPE1	A	Corey	0.291667	0.625
12	RPE2	A	Jessica	0.291667	0.625
13	RPL1	C	Ashley B.	0.5625	0.895833
14	RPL2	X	Not Staffed
15	WPE1	A	Kiana	0.291667	0.625
16	WPE2	C	Jeremy	0.291667	0.625
17	WPL1	C	Lauren	0.5625	0.895833
18	WPL2	X	Not Staffed
STAFF

What I would like to do, is generate a list of values (crews) from column A in which the service time (t_min) falls within that crews shift (Start-Column D - End-Column E)

The list can start at H3 on worksheet ws_ws_core.

I will be be very appreciative of any help provided.

 

[Peter_SSs]

I've done this on a single sheet, but if it is what you want, hopefully you can adapt it.

Sub Roster()
  Dim t_min As Single
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
  
  t_min = 0.72
  a = Range("A2:E" & Range("A" & Rows.Count).End(xlUp).Row).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    If t_min >= a(i, 4) And t_min <= a(i, 5) Then
      k = k + 1
      b(k, 1) = a(i, 1)
    End If
  Next i
  Range("H3").Resize(k).Value = b
End Sub

Original data in A:E, code results in col H

Excel Workbook
	A	B	C	D	E	F	G	H
1	Staff Roster
2	CUE1	1	Kelly	0.291667	0.625
3	CUE2	X	Not Staffed	0.291667	0.625			CUL1
4	CUL1	2	Jen	0.666667	1			HPL1
5	HPE1	A	Morgan	0.291667	0.625			RPL1
6	HPE2	X	Not Staffed	0.291667	0.625			WPL1
7	HPL1	C	Tessa	0.5625	0.895833
8	HPL2	X	Not Staffed
9	RPE1	A	Corey	0.291667	0.625
10	RPE2	A	Jessica	0.291667	0.625
11	RPL1	C	Ashley B.	0.5625	0.895833
12	RPL2	X	Not Staffed
13	WPE1	A	Kiana	0.291667	0.625
14	WPE2	C	Jeremy	0.291667	0.625
15	WPL1	C	Lauren	0.5625	0.895833
16	WPL2	X	Not Staffed
Roster