Deleting staff names when they are on annual leave

[Bill Stenson]

Hi,

This is my first post on here, I hope you can help

I am building a staffing plan for work. I have the weekly plan setup on sheet 1, which looks like this:

[TABLE="width: 724"]
<tbody>[TR]
[TD][TABLE="width: 724"]
<tbody>[TR]
[TD]DUTY[/TD]
[TD][/TD]
[TD]M[/TD]
[TD]T[/TD]
[TD]W[/TD]
[TD]Th[/TD]
[TD]F[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]L. Fawcett[/TD]
[TD]L. Fawcett[/TD]
[TD]L. Fawcett[/TD]
[TD]L. Fawcett[/TD]
[TD]L. Fawcett[/TD]
[TD]L. Fawcett[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]S. Mead[/TD]
[TD]S. Mead[/TD]
[TD]S. Mead[/TD]
[TD]S. Mead[/TD]
[TD]S. Mead[/TD]
[TD]S. Mead[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]S. Wood[/TD]
[TD]S. Wood[/TD]
[TD]S. Wood[/TD]
[TD]S. Wood[/TD]
[TD]S. Wood[/TD]
[TD]A. Blundell[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]P. Lee[/TD]
[TD]P. Lee[/TD]
[TD]P. Lee[/TD]
[TD]P. Lee[/TD]
[TD]P. Lee[/TD]
[TD]P. Lee[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The annual leave for the week is on sheet 2 in the same format. I have been trying (and failing) to build a macro that will read the annual leave list for each day of the week, and delete anyone that is on leave on a given day.

The names in the weekly plan on sheet 1 are selected from a data validation so I can't use any formulas in the cells.

Any advice will be greatly appreciated

Bill

 

[michaelsmith559]

Based on your description, I named sheet1 "Staff Plan" & sheet2 "Annual Leave". I also am assuming that sheet2 is setup like sheet1, the only difference being is missing names for people who are on leave. If this is the case, see if this works for what you want to do. You may have to change the sheet names and cell references in the code to fit your needs.

Sheet1 "Staff Plan" before macro:

Excel 2012
	A	B	C	D	E	F	G	H
1	DUTY	M	T	W	Th	F	S
2	1	L. Fawcett	L. Fawcett	L. Fawcett	L. Fawcett	L. Fawcett	L. Fawcett
3	2	S. Mead	S. Mead	S. Mead	S. Mead	S. Mead	S. Mead
4	3	S. Wood	S. Wood	S. Wood	S. Wood	S. Wood	A. Blundell
5	4	P. Lee	P. Lee	P. Lee	P. Lee	P. Lee	P. Lee
6
Staff Plan

Sheet2 "Annual Leave":

Excel 2012
	A	B	C	D	E	F	G	H
1		M	T	W	Th	F	S
2		L. Fawcett			L. Fawcett	L. Fawcett	L. Fawcett
3					S. Mead	S. Mead
4		S. Wood		S. Wood	S. Wood
5			P. Lee	P. Lee			P. Lee
6
7
Annual Leave

Sub Staff_Plan_Annual_Leave()
Dim i As Long, j As Long, lastcol As Long, lastrow As Long

lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
lastrow = Cells(Rows.Count, 2).End(xlUp).Row

For i = 2 To lastcol
    For j = 2 To lastrow
        If Sheets("Annual Leave").Cells(j, i).Value = Sheets("Staff Plan").Cells(j, i).Value Then
            Else
        If IsEmpty(Sheets("Annual Leave").Cells(j, i).Value) = True Then
            Sheets("Staff Plan").Cells(j, i).ClearContents
        End If
        End If
    Next j
Next i
        
End Sub

Sheet1 "Staff Plan" after macro runs:

Excel 2012
	A	B	C	D	E	F	G	H
1	DUTY	M	T	W	Th	F	S
2	1	L. Fawcett			L. Fawcett	L. Fawcett	L. Fawcett
3	2				S. Mead	S. Mead
4	3	S. Wood		S. Wood	S. Wood
5	4		P. Lee	P. Lee			P. Lee
6
Staff Plan