Calculating annual leave

[englishkid19]

HI All,
I have a series of workbooks all with god knows how many worksheets on. Each worksheet represents an employee. Their is then a table on the worksheet which represents their sickness, annual leave and days off. Basically I have been tasked with looking through all 2000 worksheets and finding out the date at which an employee reaches 75 hours annual leave and 150 hours annual leave. Basically I have been working on code to sequentially work down through the (A/L) annual leave rows and go through each cell adding or minusing their accrued annual leave against a variable. When the variable passes 75 and 150 I want it to print the month and day on the right hand side of the table, Each cell represents a day and each row represent month , so you have to some type of offset when the variable triggers.

I attach the table below. Any help appreciated cant think I want to count this up 2000 times.

	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	Total
April
A/L	9.50																															9.50
Sickness																																0.00
O/T			8.50						1.50							9.00								8.25								27.25
May
A/L						DO																		9.50			9.50					19.00
Sickness																																0.00
O/T			5.50					0.50		0.50				7.50						0.50	0.50	10.00									10.00	35.00
June
A/L						9.50	9.50			9.50	D/O	9.50	9.00	9.50			9.50	C	D/O		2.00			9.50			D/O					77.50
Sickness																																0.00
O/T			9.25	0.50	0.50													1.00	10.50							1.00	8.00					30.75
July
A/L					D/O			D/O								D/O			3.00					D/O								3.00
Sickness																																0.00
O/T				0.50	7.00											10.00								9.50								27.00
August
A/L	D/O	9.00						9.00	D/O			D/O								D/O						9.50		D/O	9.50	9.50		46.50
Sickness																																0.00
O/T														0.50						9.50	0.50		0.50				0.50					11.50
September
A/L		9.50	9.50	9.00	D/O	9.50			9.50				D/O			D/O								D/O								47.00
Sickness																																0.00
O/T									4.50				5.50											5.00								15.00
October
A/L		D/O								D/O								D/O			D/O								D/O			0.00
Sickness																																0.00
O/T														0.50											1.00							1.50
November
A/L						D/O								D/O							9.00	D/O			D/O	9.50						18.50
Sickness																																0.00
O/T																																0.00
December
A/L			D/O							-1.00	D/O	-0.50							2.50				9.50	9.50	9.50	9.00	D/O			D/O		38.50
Sickness																																0.00
O/T										1.00		0.50							-2.50													-1.00
January
A/L	9.50		3.50				D/O								D/O								D/O								D/O	13.00
Sickness																																0.00
O/T														0.50		0.50											0.50					1.50
February
A/L			D/O								D/O								D/O								D/O	9.50				9.50
Sickness																																0.00
O/T																			7.50													7.50
March
A/L			D/O	9.50			9.00			D/O	9.50	C	C	C				D/O								D/O						28.00
Sickness																																0.00
O/T																	0.50	9.50							0.50		0.25					10.75

<colgroup><col width="64" style="width: 48pt;"> <col width="26" style="width: 20pt; mso-width-source: userset; mso-width-alt: 950;" span="9"> <col width="28" style="width: 21pt; mso-width-source: userset; mso-width-alt: 1024;"> <col width="26" style="width: 20pt; mso-width-source: userset; mso-width-alt: 950;" span="21"> <col width="77" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;"> <tbody> </tbody>

 

[KeepTrying]

Hi,

I'll give it a try (I use Excel 2010). When I wrote code I assumed:
- you mentioned "Their is then a table on the worksheet...". I wrote code for data range not for formatted table (table can be created in Home menu by selecting "Format as table" but my code will not work for formatted table)
- data range for me is range "A1:AG49"
- code goes through all cells for rows "A/L" and summarize numbers. When result is 75 and 150: it writes the result on every sheet in range "A1:AJ2", e.g.:

Excel 2010

	AI	AJ
1	A/L, 75 hours:	11-Apr
2	A/L, 150 hours:	25-Jun

<tbody>
</tbody>

Sub AnnualLeaveCount()

Dim EmployeeSheetName
Dim Elem
Dim i As Integer, j As Integer
Dim vCount As Double

'75 hours annual leave and 150 hours annual leave

Application.ScreenUpdating = False

EmployeeSheetName = Array("JohnSmith_Test", "JohnSteven_Test") 'Add sheet names with employee names

For Each Elem In EmployeeSheetName
    With Sheets(Elem)
        .Range("ai1:aj2").Cells.Clear
        .Range("ai1") = "A/L, 75 hours:"
        .Range("ai2") = "A/L, 150 hours:"
    End With
    vCount = 0
    For i = 2 To Sheets(Elem).UsedRange.Rows.Count - 1
        If Sheets(Elem).Cells(i, 1) = "A/L" Then
            For j = 2 To Sheets(Elem).UsedRange.Columns.Count - 1
                If Application.IsNumber(Sheets(Elem).Cells(i, j).Value) And Sheets(Elem).Cells(1, j) <> "Total" Then
                    vCount = vCount + Sheets(Elem).Cells(i, j).Value
                End If
                If vCount >= 75 Then
                    Sheets(Elem).Range("aj1") = Sheets(Elem).Cells(i - 1, 1) & " " & Sheets(Elem).Cells(1, j)
                    GoTo Next_
                End If
            Next j
        End If
    Next i
Next_:
    vCount = 0
    For i = 2 To Sheets(Elem).UsedRange.Rows.Count - 1
        If Sheets(Elem).Cells(i, 1) = "A/L" Then
            For j = 2 To Sheets(Elem).UsedRange.Columns.Count - 1
                If Application.IsNumber(Sheets(Elem).Cells(i, j).Value) And Sheets(Elem).Cells(1, j) <> "Total" Then
                    vCount = vCount + Sheets(Elem).Cells(i, j).Value
                End If
                If vCount >= 150 Then
                    Sheets(Elem).Range("aj2") = Sheets(Elem).Cells(i - 1, 1) & " " & Sheets(Elem).Cells(1, j)
                    GoTo Next2_
                End If
            Next j
        End If
    Next i
Next2_:
Next Elem

Application.ScreenUpdating = True

End Sub

I hope that's what you request.