Complicated Random Loop Macro

lumpland · Jan 6, 2010

ther; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-520092929 1073786111 9 0 415 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin-top:0cm; margin-right:0cm; margin-bottom:10.0pt; margin-left:0cm; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-fareast-language:EN-US;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-fareast-language:EN-US;} .MsoPapDefault {mso-style-type:export-only; margin-bottom:10.0pt; line-height:115%;} @page Section1 {size:595.3pt 841.9pt; margin:72.0pt 72.0pt 72.0pt 72.0pt; mso-header-margin:35.4pt; mso-footer-margin:35.4pt; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style> Hi everyone!

I've spent a long time working on a roster for my team at work. I've done just about as much as I am capable of with my lack of VB knowledge. To sum up what I am desperate for is a macro that can fill in cells randomly, based on criteria eg. countif, if cell contains checks, etc. It's complicated because there are over 500 employees working up to 6 days a week and about a million different tasks to be rostered. As the roster is currently entered manually it takes many many hours to figure out a week's roster, because you can only have so many staff on each task depending on the work loads, fair rotation, and annual leave.

I tried many different things using nested if, countif, random, rank formulas but when it's starting to look good I get circular references. So that's why I'm turning to VBA.

Say I generate random numbers and then the cell shows a task based on the number in another cell. Can I get a code that can keep generating random rosters until certain criteria are met, like task counts and counting how many times each employee performs a task?
I understand it’s a lot to ask but I appreciate any help.
-lumpland

BrianB · Jan 7, 2010

You have not defined your method exactly.
A good way of approaching a problem like this is to mirror the manual method you use.

Let's say you have 2 lists - Tasks/hours and Employees/ working hours. The employee list would need a note of an employee being on holiday.

Code:

"Pseudo code"
 
for Task = 1 to 100
assigned=false
 
Do while while assigned=false
    generate  random number
    choose the employee in that number position in the list
'-
    Check not on holiday
    Check number of working hours available
    If that employee has enough hours left
        do whatever other checks you need
        If still Ok
            deduct the task hours
            assign the task to the employee
            assigned = True
        End If
    End If
Loop
 
 
 
Next Task

I don't like the random idea much. Could take too long.
I would prefer to use an "employee" loop instead. Could be easier to check (employee 1 gets allocated first)

Code:

for Task = 1 to 100
assigned=false
 
For employee = 1 to 500
       ' other code here
       If assigned = true then Exit For
Next
 
Next Task

lumpland · Jan 7, 2010

Thanks for your reply BrianB. I agree that the random idea would be too much pain for little gain. I was just worried that employee 1 would always get the first task on the first day and that's why I wanted it to be random. Sorry, I should explain my roster in more detail.

In my team there are 53 staff. We have 24 different tasks that can be entered. I say 15 are ones which I would like to be 'auto filled' and the remaining are things like different kinds of leave and training, which would be filled in manually. The way it's laid out is the names are down a single column with the tasks to be performed in columns to the right running under the dates, from January to June. I gave each task a two letter code. To the left are columns for which tasks they can perform with 1s indicating they can, and I use the autofilter to sort them when filling the roster in.

Also the column immediately to the left of their names is the rostered day off column. All full time employees are assigned a letter from A to S. Running along the top of the sheet are the letters A to S. So whenever their letter comes up they have a day off, which works out to once every 4 weeks. Part timers have their non work days, which I fill in manually and no rostered day off. The casuals can work up to 40 hours a week. Everyone starts the shift at the same time for 8 hour shifts so I don't have to worry about shift times, except casual hours, depending on the work load.

Underneath the grid where I fill in the tasks are the counts for each task for everyday. Also there are rows running underneath each casual's task row which is for how many hours they worked that day. Then because our team doesn't work Saturdays, I use those columns to add up their weekly hours total and at the bottom of this column I have total casual hours, average hours and hours saved (casuals x 40 hours - hours worked).

As you laid out I would need a code that checks if the cell that intersects the employee's name and date to see if it's already filled in, then check to see what the employee is capable of doing, then check how many employees are needed for a task, check if the employee has performed that task so many times in the last 5 days, fill that cell and move on

.

I do have a seperate sheet, which I use to fill in the volume of work for each day and in the same column for each day it will work out how many people are needed for each task, based on an average work rate eg. 12000 volume, 180 per hour = (12000 / 180 / 7.5) = 8.88 employees.

The last sheet I use is the printout sheet. Which shows one day, a header for each task and the names of the people underneath each task. I use formulas to fill in the printout and it works nicely.

I hope you can make sense what I have written here.

Complicated Random Loop Macro

lumpland

New Member

Excel Facts

BrianB

Well-known Member

lumpland

New Member

Similar threads

Forum statistics

Share this page

Complicated Random Loop Macro

lumpland

New Member

Excel Facts

BrianB

Well-known Member

lumpland

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock