Attendance tracking

[yogananda.muthaiah]

Dear Experts,

I would require all your help in getting this requirement complete


List of Requirement

1 ) Need to alter the formula for Range from 1st to 30th April 2012 where ever Saturday and Sunday as "WO" in Brown highlighted column - Refer Tracker Sheet
2) Employee No : 42244 as taken LOP leave then it should update total no of days as LOP leave taken (including saturday and sunday ) and
in Total Leave summary should reflect as 6 days instead of 4 days - Refer Upload Sheet
3) In Resigned sheet - Once Employee code no inputs in "A" Column the Flash or Blinking warning should come ( Already VB code is done only alternation needs to be done)
4) If any Employee takes AL in continous 5days in date range then should highlight as BL in total leave summary column
5 ) In Holiday List Sheet - Once the Holiday date is entered , then H should reflect in Tracker sheet as per the Date (Example is in Holiday list sheet)

[yogananda.muthaiah]

Hi,

Please someone put ur hands on it......

[rvasquez]

Hey there,

For the second request of your post where are the subtotals for your toal leave? I see where you have formulas referencing certain types of leave but it doesn't seem like you have a total leave column.

Also, can you please clarify number3? Obviously there's an employee code column but does there need to be a formula that looks up an employee code that matches a certain criteria and if it is there then it flashes red? I'm not quite sure as to what you are asking.



Thanks!

RVASQUEZ

[yogananda.muthaiah]

Hi

We will go by 1 by 1 of my requirements to solve

1) from 1st to 30th date range having formula which needs to altered to get WO(weekly off) for brown colored cells. Please help

[yogananda.muthaiah]

Hi,

Can anyone try this out

[yogananda.muthaiah]

Hi,

Anybody help me out

[StevenM]

If you're interested in a VBA solution, I would give it a try. But I don't work with UDFs (User Defined Functions).

[yogananda.muthaiah]

Hi Steven

Thank you responding immed.
Your welcome do with any option through VBA or UDF.

[StevenM]

Does this work for you?

'---------------------------------------------------------------------------------------
' Procedure : AddHolidays2Tracker
' Purpose   : Mark the Holidays on Tracker Schedule
'---------------------------------------------------------------------------------------
'
Sub AddHolidays2Tracker()
    Dim wb As Workbook
    Dim nLastRow As Long, nRow As Long, nDay As Long
    Dim nLastRow2 As Long, nRow2 As Long
    
    Set wb = ThisWorkbook
    With wb.Worksheets("Holiday List")
        nLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    'Holiday List of Date begin on Row 4 (This part redundant, since all dates the same)
    For nRow = 4 To nLastRow
        nDay = Day(wb.Worksheets("Holiday List").Cells(nRow, "A"))
        With wb.Worksheets("Tracker")
            nLastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row
            'Emp Codes begin on Row 9
            For nRow2 = 9 To nLastRow2
                'The Day Column is one more than the Day
                .Cells(nRow2, nDay + 1) = "H"
                .Cells(nRow2, nDay + 1).Interior.ColorIndex = 3 'red (Holiday color?)
            Next nRow2
        End With
    Next nRow
End Sub

1) from 1st to 30th date range having formula which needs to altered to get WO(weekly off) for brown colored cells. Please help

Could you explain this for me?

[yogananda.muthaiah]

Gr8 Steven you have solved my 5th requirement

Now there is slight change in Holiday list VBA script where it should match the location and needs to "H" in given date range

For Example : For Location like Belegaum,Kottyam as Holiday date , it should update in tracker sheet as H in given dates.

[yogananda.muthaiah]

Please help out

[StevenM]

Sub AddHolidays2Tracker()
    Dim wb As Workbook
    Dim nLastHoliRow As Long, nHoliRow As Long
    Dim nLastTrackRow As Long, nTrackRow As Long
    Dim nDay As Long, sLocation As String
    
    Set wb = ThisWorkbook
    With wb.Worksheets("Holiday List")
        nLastHoliRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    'Holiday List of Date begin on Row 4
    For nHoliRow = 4 To nLastHoliRow
        nDay = Day(wb.Worksheets("Holiday List").Cells(nHoliRow, "A"))
        sLocation = wb.Worksheets("Holiday List").Cells(nHoliRow, "B")
        With wb.Worksheets("Tracker")
            nLastTrackRow = .Cells(.Rows.Count, "A").End(xlUp).Row
            'Emp Codes begin on Row 9
            For nTrackRow = 9 To nLastTrackRow
                'If Locations Match
                If sLocation = .Cells(nTrackRow, "B") Then
                    'The Day Column is two more than the Day
                    .Cells(nTrackRow, nDay + 2) = "H"
                End If
            Next nTrackRow
        End With
    Next nHoliRow
End Sub

[StevenM]

This code takes the Upload info and adds it to the Tracker Sheet. Check it over and see if it works for you.

Sub AddUpload2Tracker()
    Dim wb As Workbook, rg As Range
    Dim nLastUploadRow As Long, nUploadRow As Long
    Dim nEmpCode As Long, dtStartDOL As Date, dtEndDOL As Date, sType As String
    Dim nDay As Long, nTrackRow As Long, dt As Date
    
    Set wb = ThisWorkbook
    
    With wb.Worksheets("Upload")
        nLastUploadRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        ' Emp.Codes begin on row 3
        For nUploadRow = 3 To nLastUploadRow
            nEmpCode = .Cells(nUploadRow, "B")
            dtStartDOL = .Cells(nUploadRow, "D")
            dtEndDOL = .Cells(nUploadRow, "E")
            sType = .Cells(nUploadRow, "F")
            
            Set rg = wb.Worksheets("Tracker").Cells.Find(nEmpCode)
            If rg Is Nothing Then
                MsgBox "Could not locate " & nEmpCode
            Else
                nTrackRow = rg.Row
                dt = dtStartDOL
                Do
                    nDay = Day(dt)
                    'Day one on the Tracker Sheet is column "C" = 3
                    wb.Worksheets("Tracker").Cells(nTrackRow, nDay + 2) = sType
                    'if day is end of month then exit do
                    If dt = DateSerial(Year(dtStartDOL), Month(dtStartDOL) + 1, 0) Then Exit Do
                    'Add one day to "dt"
                    dt = DateAdd("d", 1, dt)
                Loop While (Day(dt) <= Day(dtEndDOL))
            End If
        Next nUploadRow
    End With
End Sub

[StevenM]

3) In Resigned sheet - Once Employee code no inputs in "A" Column the Flash or Blinking warning should come ( Already VB code is done only alternation needs to be done)

Could you expand on what you need here?

[yogananda.muthaiah]

I am observing end to end of your code to understand and sure will come back if any other requirement is required from you in next 1hr.

Thank you onceagain steven

[yogananda.muthaiah]

Thank you steven for your valuable effort . your really great person for us