Guidance please!

rostergoblin

New Member
Joined
Apr 20, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

COwen

Board Regular
Joined
Feb 27, 2020
Messages
225
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,454
Messages
5,596,228
Members
414,047
Latest member
debbos

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top