VBA Module to run work allocation

[twinkle99]

I have a spreadsheet (Sheet1) which contains a breakdown of worktypes.

I have another Spreadsheet (Sheet2) which contains a breakdown of Staff.

Sheet1 is linked to a database table and updates every morning.

The total number of work items have to be allocated equally between the staff on a daily basis. At present, the work is allocated manually by completing Sheet2 with the appropriate do and check columns under each worktype until each member of staff has approximately 7 hours worth of work.

I would like to automate the completion of Sheet2 using VBA Programming. This can be done randomly. I am not really sure where to start with this but have a couple of ideas which could be incorporated into the module:

1. A tick box which allows the user to select the staff members available for Doing work.
2. A tick box which allows the user to select the staff members available for Checking work.

These tick boxes could eliminate any staff who are either sick, on holiday or on other jobs.

Important:
1. All work has to be checked by a different staff member to the doer.
2. The Module should complete do and check numbers for each worktype under Columns C to T (Sheet2) for each Staff Member (rows 6 to 15).
3. The total allocated work time for each staff member (column U) should be as close to 7 hrs as possible for each staff.
4. For simplicity, the module should assume that staff can do and check all types of work.
5. If there is more work than staff available, the Module should return the surplus work time (over 7 hrs) allocated to each staff member (Column V)
6. If there is less work than staff available, the Module should return the deficit work time not allocated (under 7 hrs) to each staff member (Column W)

Many Thanks

staff.xls
	B	C	D	E	F	G
3	worktype	noofitems	unittime	unittime	totaltimeto	totaltimeto
4			todo(min)	tocheck(min)	dowork(min)	checkwork(min)
5	Returns	26	15.00	20.00	390	520
6	Notices	2	10.00	10.00	20	20
7	Leavers	0	50.00	25.00	0	0
8	Calls	28	5.00	10.00	140	280
9	Deliveries	2	100.00	35.00	200	70
10	Copies	0	20.00	20.00	0	0
11	Stamps	16	10.00	15.00	160	240
12	Contributions	12	50.00	30.00	600	360
13	Training	4	50.00	40.00	200	160
14	total	90	310.00	205.00	1710	1650
Sheet1

staff.xls
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
3
4		Returns		Notices		Leavers		Calls		Deliveries		Copies		Stamps		Contributions		Training		Totalallocated	SurplusWork	DeficitWork
5		Do	Check	Do	Check	Do	Check	Do	Check	Do	Check	Do	Check	Do	Check	Do	Check	Do	Check	worktime	Allocation(>7hrs)	Allocation(<7hrs)
6	Steve
7	Terry
8	Leo
9	Patricia
10	Carly
11	Angela
12	Rory
13	Helen
14	Charles
15	Fiona
16
17	total
Sheet2

 

[tusharm]

I could be wrong but this is not something for which you will get a free solution. This class of problems is fairly complex to solve.

You may want to look at Solver (an add-in bundled with Excel). It is designed to work with these kinds of problems. The downside is that except for really simple problems, Solver is not as easy to 'program' as creating any ole Excel model and applying Solver to it.

 

[twinkle99]

Thanks Tusharm

I wasn't expecting a full solution to this, just really need some guidance on a) whether it would be possible and b) how I could start this.

Thanks