Guidance please!

[rostergoblin]

Hi. I have just started to teach myself VBA, with the intention of writing macros to pull information from my company's rolling roster.

Can anyone point me in the right direction of how to achieve what I'm trying to do.

I think I'll need to use an array to hold the data in the roster. However if I do that, I need a way to define each variable and how it relates to another in the array.

For instance. We have 80 staff members. Each staff member has a line number which changes each week numerically. So if I'm on line 37 this week, the next week I will be on line 38. The colleague who was on line 38 this week moves to 39, etc. The colleague who was on line 80, then moves to line 1. Hence the "rolling" nature of the roster.

Facts about the data:
Line 1 or Line 67 or any particular line, is unchanging. The data remains constant. The person working the line changes each week.

So for example line 1 looks like this...
Monday: OFF, Tuesday: Early, Wednesday: Early, Thursday: Mid, Friday: Mid, Saturday: Late, Sunday: OFF
Line 2 will follow...
Monday OFF, Tuesday: OFF Wednesday: Late etc etc.

What I am trying to do is write Macros that can print all the 80 lines that are OFF on a Wednesday, or Friday etc etc.
I need to write a Macro that can report which lines are on their first day after a day off.
I need to write a Macro that can report which lines are on their last day at work before a day off
I need to write a Macro that can use a date function that calculates who will be on an early shift on Monday 6 months into the future... or any date in the future... via a message box


Basically I need someone to tell me the best way to put unchanging data in an array that is 7 columns and 80 rows, and then (probably?) use a bunch of if then statements.

The purpose of these macros is to help colleagues swap shifts with each other.

There are a whole bunch of rostering rules that need to be followed, like for instance, you may not work an early the day after working a late, as we have a minimum rest period.

I need to write a Macro that will say... you can't swap your early with Bob on Thursday next week, as he was on a late the day before, and this is against rostering rules. However, these are the people who could work your early shift according to the roster. Then anyone on shift 1-30 for instance could swap with me, but anyone on shift 31-80 couldn't because of predefined rules.

Could anyone suggest the types of things I need to be putting into macros to reference information from my grid?

Thanks for any help. Sorry if the above is confusing. I'm doing my nut

Regards

 

[COwen]

Have you tried recording macros? it is a great way to teach yourself VBA. A friend of mine learned VBA about 15 years ago (before there were so many resources) by recording macros.