Need a macro to count number of occurrences

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gav123 - 21 Nov 2007 10:31 GMT

Hi,

This could be tricky to explain but I'll give a shot...

I have an absence tracker, the workbook contains a Summary sheet and 12
month sheets (January-December)

On the summary sheet..
I'm counting the number of sick days for each employee per month (Which is
easy enough) my boss wants to count the number of occurances (Where an
occurance is 1 individual sick day or if the employee is of sick for a
continous period)

Month sheet set-up is..

Employee names are in column C with the first name at row 10 and the sick
days, etc. are below that in row 11, the second name is in C12 and sick days
in C13 (continues down for 200 employees)and the dates are across the top,
with the day's at D8:AH8 (31 days in January) each of these sheets is set up
the same with the last column adjusted for the different number of day's in
each month.

So how can I count the number of occurances of sickness for each employee
for the year, with the results being displayed in the Summary sheet AL10 (for
the first employee, AL12 for the second employee, etc?

Hope that I have explained the problem enough.

Any help or advice on this would be appreciated.

Thanks in advance,

Gav.

Reply to this Message

Tredown - 21 Nov 2007 11:17 GMT

Hi Gav
Try this,
Set conditional format for each day as follows; Cell Value is equal to ="S"
format the cell color or font color as you wish.
To enter sickness type the letter S into the correct cell. To enter if more
than one day merge cell then type the letter S
Put a column at the end of each month giving you the total of occassions
placing the following formula =COUNTIF (B5:AE5,"S") Change the range to suit.

> Hi,
>
[quoted text clipped - 29 lines]
>
> Gav.

Reply to this Message

Per Jessen - 21 Nov 2007 11:47 GMT

> Hi,
>
[quoted text clipped - 33 lines]
>
> Gav.

Hi

Asuming that "s" in actual cell indicateing a day sick

This routine will give you the number of periods an employee is sick in cell
AJ11 and down for each month.

Use the "Sum" function on your summary sheet to calculate the total sick
periods.

Option Explicit

Const Sick As String = "s"
Dim StartCell As Range
Dim LastColumn As Long
Dim Col As Long
Dim r As Long
Dim IsSick As Long
Dim Test As String
Dim Employees As Long
Dim e
Dim MyMonth

Sub CountSickPeriods()
Set StartCell = Range("D11")
Employees = 200

For MyMonth = 1 To 12
Sheets(MyMonth).Select ' Assuming "Summary" sheet is number 13
r = StartCell.Row
LastColumn = StartCell.Offset(-3, 0).End(xlToRight).Column
For e = 1 To Employees

For Col = StartCell.Column To LastColumn
Test = Cells(r, Col + 1).Value
If Cells(r, Col).Value = Sick And Cells(r, Col + 1).Value <> "s"
Then
IsSick = IsSick + 1

End If
Next
Cells(r, 36) = IsSick
IsSick = 0
r = r + 2
Next
Next
End Sub

Regards,

Per

Reply to this Message

Gav123 - 21 Nov 2007 15:25 GMT

Thanks Per, this pointed me in the right direction.. with some slight
modification it does exactly what I need..

Option Explicit

Const Sick As String = "s"
Dim StartCell As Range
Dim LastColumn As Long
Dim Col As Long
Dim r As Long
Dim IsSick As Long
Dim Test As String
Dim Employees As Long
Dim e
Dim MyMonth

Sub CountSickPeriods()
Set StartCell = Range("D11")
Employees = 200
r = StartCell.Row
For e = 2 To Employees
Sheet1.Cells(r, 38) = 0
r = r + 2
Next

For MyMonth = 2 To 13
Sheets(MyMonth).Select
r = StartCell.Row
LastColumn = StartCell.Offset(-3, 0).End(xlToRight).Column

For e = 1 To Employees

For Col = StartCell.Column To 34
Test = Sheets(MyMonth).Cells(r, Col + 1).Value

If Sheets(MyMonth).Cells(r, Col).Value = "s" And
Sheets(MyMonth).Cells(r, Col + 1).Value <> "s" Then
IsSick = IsSick + 1
End If
Next
Sheet1.Cells(r, 38) = Sheet1.Cells(r, 38) + IsSick
IsSick = 0
r = r + 2
Next
Next
End Sub

Once again Thank you

Gav.

> > Hi,
> >
[quoted text clipped - 85 lines]
>
> Per

Reply to this Message

Per Jessen - 22 Nov 2007 17:55 GMT

Hi Gav

"> Thanks Per, this pointed me in the right direction.. with some slight

> modification it does exactly what I need..

Thanks for your reply.

I just realised that if an employee is sick on the last day of a month and
the first day of next month, it will count as 2 periods :-(

To fix this "bug" add this line after "IsSick=IsSick+1"

If MyMonth < 13 And Col = LastColumn And Sheets(MyMonth + 1).Cells(r, 4) =
"s" Then IsSick = IsSick - 1

Regards

Per

Reply to this Message

Don Guillett - 21 Nov 2007 14:46 GMT

A post of mine today from a similar question. Modify to suit

If you want to sum col J for each worker no matter where he is in col B,
then I think I would use a for/each macro something like

Sub getsumforeachemployee()
For Each n In Sheets("summary").Range("a2:a4")
ms = 0
For i = 2 To Sheets.Count
ms = ms + Sheets(i).Columns(2).Find(n).Offset(, 8)
Next i
'MsgBox ms
n.Offset(, 1) = ms
Next n
End Sub

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Hi,
>
[quoted text clipped - 33 lines]
>
> Gav.

Reply to this Message