trend analysis

roninn75
3StarLounger
Posts: 244
Joined: 15 Feb 2013, 08:25

trend analysis

Post by roninn75 »

good day
In the attached sample file, i am tracking absenteeism per day. Shifts are split into dayshift and night shift. I want to run a macro to check each sheet for the month and populate a seperate MasterData workbook to do a trend analysis.
the MasterData file has a sheet named MasterData with columns A:N being populated. the code that is doing this is as below:

Code: Select all

Option Explicit

Sub ProcessAttendanceData()
    Dim masterWorkbook As Workbook
    Dim masterSheet As Worksheet
    Dim dailyWorkbook As Workbook
    Dim dailySheet As Worksheet
    Dim folderPath As String
    Dim fileName As String
    Dim outputRow As Long
    Dim dailyDate As Date
    Dim shiftType As String
    Dim shiftOD As String
    Dim totalOnDuty As Double
    Dim totalSupervisors As Double
    Dim totalSeniors As Double
    Dim totalOperators As Double
    Dim sickCount As Double
    Dim adhocLeaveCount As Double
    Dim annualLeaveCount As Double
    Dim frlCount As Double
    Dim courseCount As Double
    Dim overtimeCount As Double
    Dim rng As Range
    Dim rng2 As Range
    Dim cell As Range
    
    ' Folder path containing daily workbooks
    folderPath = "C:\Users\rfourie1\Documents\Fire\Absenteeism\Control\" ' path to daily workbook
    
    ' Open MasterData workbook
    Set masterWorkbook = Workbooks.Open("C:\Users\rfourie1\Documents\Fire\Absenteeism\Control\MD_Base_Control.xlsx") ' path to where master data is stored
    Set masterSheet = masterWorkbook.Sheets("MasterData")
    
    ' Find the next empty row in the MasterData Sheet
    outputRow = masterSheet.Cells(masterSheet.Rows.Count, 1).End(xlUp).Row + 1
    
    ' Loop through all Excel files in the folder
    fileName = Dir(folderPath & "*.xlsm")
    Do While fileName <> ""
        ' Open the daily workbook
        Set dailyWorkbook = Workbooks.Open(folderPath & fileName)
        
        ' Loop through each sheet in the daily workbook
        For Each dailySheet In dailyWorkbook.Sheets
            ' Get the date from the sheet name
            If dailySheet.Name <> "Ranges" And dailySheet.Name <> "Summary" And dailySheet.Name <> "Roster" And dailySheet.Name <> "Template" And dailySheet.Name <> "First" And dailySheet.Name <> "Availability" And dailySheet.Name <> "Last" Then
            ' dailyDate = DateValue(dailySheet.Name)
            dailyDate = DateValue(CleanSheetName(dailySheet.Name))
            
            ' Read required data from the daily sheet
            shiftType = dailySheet.Range("A4").Value
            shiftOD = dailySheet.Range("F5").Value
            totalOnDuty = dailySheet.Range("N5").Value
            totalSupervisors = dailySheet.Range("J6").Text
            totalSeniors = dailySheet.Range("L6").Value
            totalOperators = dailySheet.Range("N6").Value
            
            ' Count occurrences in E9:E22
            Set rng = dailySheet.Range("E9:E22")
            sickCount = 0
            adhocLeaveCount = 0
            annualLeaveCount = 0
            frlCount = 0
            courseCount = 0
            
            For Each cell In rng
                Select Case LCase(cell.Value)
                    Case "Sick"
                        sickCount = sickCount + 1
                    Case "Adhoc Leave"
                        adhocLeaveCount = adhocLeaveCount + 1
                    Case "Annual Leave"
                        annualLeaveCount = annualLeaveCount + 1
                    Case "FR Leave"
                        frlCount = frlCount + 1
                    Case "Course"
                        courseCount = courseCount + 1
                End Select
            Next cell
            
            ' Count occurrences in F9:F22
            Set rng2 = dailySheet.Range("F9:F22")
            overtimeCount = 0
            
            For Each cell In rng2
                If cell.Value <> "" Then
                    overtimeCount = overtimeCount + 1
                    
                End If
            Next cell
            
            ' Write data to the MasterData Sheet
            With masterSheet
                .Cells(outputRow, 1).Value = dailyDate
                .Cells(outputRow, 2).Value = Format(dailyDate, "dddd") ' Day name
                .Cells(outputRow, 3).Value = shiftType
                .Cells(outputRow, 4).Value = "shiftOD" 'shift on duty
                .Cells(outputRow, 5).Value = "overtimeCount" ' Overtime
                .Cells(outputRow, 6).Value = sickCount
                .Cells(outputRow, 7).Value = courseCount
                .Cells(outputRow, 8).Value = annualLeaveCount
                .Cells(outputRow, 9).Value = adhocLeaveCount
                .Cells(outputRow, 10).Value = frlCount
                .Cells(outputRow, 11).Value = totalOnDuty
                .Cells(outputRow, 12).Value = totalSupervisors
                .Cells(outputRow, 13).Value = totalSeniors
                .Cells(outputRow, 14).Value = totalOperators
            End With
            
            ' Move to the next row in MasterData Sheet
            outputRow = outputRow + 1
            End If
        Next dailySheet
        
        ' Close the daily workbook
        dailyWorkbook.Close SaveChanges:=False
        
        ' Get the next file
        fileName = Dir
    Loop
    
    ' Save and close the MasterData workbook
    masterWorkbook.Close SaveChanges:=True
    
    MsgBox "Data processing completed!", vbInformation
End Sub

I've managed to complete the code to check the dayshift but not yet the night shift... also, i havent yet managed to count the overtime and display the particular shift

Code: Select all

shiftOD = dailySheet.Range("F5").Value
any assistance to compile this properly would be appreciated.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 80088
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: trend analysis

Post by HansV »

I don't understand how this can work. The code loops through all sheets of the "daily" workbook, and executes this line:

Code: Select all

dailyDate = DateValue(dailySheet.Name)
But the sheet names look like this:

S2653.png

DateValue will throw an error for each of those!
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

roninn75
3StarLounger
Posts: 244
Joined: 15 Feb 2013, 08:25

Re: trend analysis

Post by roninn75 »

apologies, omitted the following code to correct the individual sheet naming convention:

Code: Select all

Function CleanSheetName(sheetName As String) As String
    ' Remove suffixes like "st", "nd", "rd", "th" from the sheet name
    Dim cleanedName As String
    cleanedName = Replace(sheetName, "st", "")
    cleanedName = Replace(cleanedName, "nd", "")
    cleanedName = Replace(cleanedName, "rd", "")
    cleanedName = Replace(cleanedName, "th", "")
    CleanSheetName = cleanedName
End Function
the code for ProcessAttendanceData routine should read:

Code: Select all

dailyDate = DateValue(CleanSheetName(dailySheet.Name))

User avatar
HansV
Administrator
Posts: 80088
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: trend analysis

Post by HansV »

What about sheets like "Ranges", "Summary" and "First"?
Best wishes,
Hans

roninn75
3StarLounger
Posts: 244
Joined: 15 Feb 2013, 08:25

Re: trend analysis

Post by roninn75 »

i am excluding those with the following:

Code: Select all

For Each dailySheet In dailyWorkbook.Sheets
            ' Get the date from the sheet name
            If dailySheet.Name <> "Ranges" And dailySheet.Name <> "Summary" And dailySheet.Name <> "First" And dailySheet.Name <> "Last" then
            ' dailyDate = DateValue(dailySheet.Name)
              dailyDate = DateValue(CleanSheetName(dailySheet.Name))
              .....

User avatar
HansV
Administrator
Posts: 80088
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: trend analysis

Post by HansV »

It would have been easier if you had included all this in the workbook that you attached...

How do we count overtime?
Best wishes,
Hans

roninn75
3StarLounger
Posts: 244
Joined: 15 Feb 2013, 08:25

Re: trend analysis

Post by roninn75 »

if an entry is made in cell F9:F22 it could be counted. thinking about it now, I could probably just have added it under H6 to display the total count.

User avatar
HansV
Administrator
Posts: 80088
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: trend analysis

Post by HansV »

How about this?

Macro.txt
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

roninn75
3StarLounger
Posts: 244
Joined: 15 Feb 2013, 08:25

Re: trend analysis

Post by roninn75 »

thank you this works