Hi All,

I am a novice to VBA but have looked at online videos and examples to try a few things.

I am dealing with a case where we are making decisions throughout the day. Staff are scheduled daily but schedules can change multiple times because of call offs, sickness, etc, and someone else has to be scheduled.

In analysing the data I need to use vlookup (or some equivalent search function) where the table array is date determined. So for example, on the 18th March I create a table called "Egtnth", on 19th "Ntnth", on 20th "Twnth" and so on. There is nothing special about the names. The dates are for the start of a shift and the schedule will have multiple days, e.g.:

Day Employee StartShift EndShift

18th Emp 1 12:00 pm 7:00 am

18th Emp 2

18th Emp 3

19th Emp 4

20th Emp 5

and so on. Is there a way I can write code in VBA to search for the first and last occurrence of 18th and create a table on adjoining data, do so for 19th etc.

I have determined how to use the CELL function with INDEX and MATCH and SUMPRODUCT to identify the start and end of a date range. That is. 2/18/2020 may start in A200 and go to A1000, then 3/19/2020 starts at A1001 and goes to A1800, and so on. The problem is that this changes throughout the day.

I need to search for employees within each day's block separately. SO I identify the date range (and optionally name it) and use it in a VLookup.
How can I make this dynamic so that no matter the changes I do not have to manually redo the VLookups? I will have to pass this on to non-Excel experts to use