VBA Solution To Find Position In A Series of Cells

[Ark68]

I am being challenged with finding a VBA solution to do a particular task. I suspect that explaining the task will be more difficult than an actual solution. I will try my best, but if I neglect to share critical information please ask for clarification. I'm not entirely sure what all would be needed to assist in find a solution.

SOP Schedule.xlsm
	A	B																					W	X	Y	Z
217	19-Jul	Sun
218	20-Jul	Mon																					E2	11:00 AM	7:00 PM	8.00
219	21-Jul	Tue																					E2	11:00 AM	7:00 PM	8.00
220	22-Jul	Wed																					E2	11:00 AM	7:00 PM	8.00
221	23-Jul	Thu																					E2	11:00 AM	7:00 PM	8.00
222	24-Jul	Fri																					L1	4:00 PM	12:00 AM	8.00
223	25-Jul	Sat																					L1	4:00 PM	12:00 AM	8.00
224	26-Jul	Sun																					L1	4:00 PM	12:00 AM	8.00
225	27-Jul	Mon																					L1	4:00 PM	12:00 AM	8.00
226	28-Jul	Tue
227	29-Jul	Wed
228	30-Jul	Thu
229	31-Jul	Fri																					E2	11:00 AM	7:00 PM	8.00
230	01-Aug	Sat																					E1	7:00 AM	3:00 PM	8.00
MASTER

This represents an employee's schedule for one pay period. Shift legs of a pay period can be dynamic. In this case its 1 day off, 8 on, 3 off, 2 on. It could be any combination of legs, but never less than 1 day off in succession. (the single day off at the beginning of the payperiod tags onto a day off at the end of the previous pay period.

What I need to do is deterine what number within the series of days off a particular day off represents. In this example, Jul 28 is position 1, July 29th is position 2 and position 3 is Jul 30th.

In my code, these blank positions (day's off) could be days that the employee has been assigned to work (beyond the regular schedule). When this happens, the vacant cell in W is populated with a value of "RSO" and the columns X, Y and Z populated accordingly with the times of the shift.

As the rate of pay is determined by the day within the legs of worked shifts, I need to know the position within the span of days off that the RSO exists. So, if the employee worked on Jul 30th, W228 = RSO. I need to determine that the position value is 3.

Some considerations:
- There can be any number of days off between legs of scheduled shifts. The number of days within any scheduled leg is variable.
- The leg of days off could include dates in bordering pay periods. In this example for instance, Jul 19 is the start of a payperiod and represents position 3 of a three day leg that starts on Fri. Jul 17th of the previous pay period.
- There could be more than one "RSO" in a leg of days off. In this example, W226 could be RSO (position 1) and then W227 could be RSO (position 2).
- only one date is processed at a time, so there would be no need to determine both positions of RSO, just the one according to the particular date being processed the application.

I would be very grateful to anyone that is able to provide direction towards a solution. I am simply a novice VBA user, and am unfamiliar with the use of different tools available for this approach.

 

[offthelip]

you haven't specified where or how you want the answer. If I have understood the question you don't need VBA, try putting this formula into column AA2, and copy it down, put 0 in AA1
=IF(OR(W2="RSO",W2=""),AA1+1,0).
If you want a vba solution can you tell me how you want the results presented

 

[Ark68]

Hi offthelip, I do appreciate your solution. I think the worksheet formula option will be adequate, and so far seems to be providing the information I need. My sample was for 1 employee (at column W), but I have 56 others in the master schedule database that I will need to apply this formula to respectively. A little bit more work, and a bit larger database, but I think it will work.