Consulting

Results 1 to 9 of 9

Thread: Attendance Spreadsheet - Custom point reduction

  1. #1

    Lightbulb Attendance Spreadsheet - Custom point reduction

    I've attached a copy of sample date and disabled a macro I have to go to the current date in row 5.
    Copy of Legrand Attendance99999.xlsm
    I've spent a decent amount of time on this spreadsheet to total attendance based on a few variables. I've been stuck for a month on the last feature.
    I have set up a situation in the sheet and explained it below, that should hopefully explain the issue better once you take a look.

    For every 90 calendar days of perfect attendance (blank cells) = One point will be removed from the total points column for each person.
    *However, if an employee's total points is at 0 at any given time, blank cells(perfect attendance) should be ignored. 0 points is the minimum.

    Rolling 365 day point drop off period (working) (Uses cell ADO:6)
    Points only count on or past the date of hire (working) (Cell ADN:6)
    Each cell has a drop down with selections for tardy (TD), call off (CO), Leave Early (LE), Continuous Absence (CA).
    TD = .5 Points |||| CO = 1 Point |||| LE/CA can be ignored (0 points)

    Currently everything is working as intended except for one feature, the 90 day perfect attendance reward.
    Weekends are excluded from the dates in row 5 so anytime I refer to 90 days it's 65 cells. (not perfect but it works for our use)

    I set up a situation in the above file that shows the problem. In row 6, they receive a call off (CO) on 1/3/2017 (1 point)
    Then they have two 90 day periods of blank cells. Followed by a call off on 8/15/2017.
    This should result in 1 point instead of 2 points.

    1/3/2017 = 1 point First 90 day period of blank cells = 0 points Now that employee has 0 points, the second 90 day blank cell period should be ignored as they can't go below 0. 8/15/2017 call off should bring total back to 1 point.
    *other rules in place already... Any cells with a drop down selection before their date of hire are ignored.
    If they have 0 points and then (5) 90 day perfect attendance periods in a row, then a call off, the total should be 1 point. Attached Files
    Last edited by formulation; 09-19-2017 at 07:44 AM.

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Rewarding
    If Points > 1 then 
    Points = Points - 1
    ElseIf Points <= 1 then Points = 0
    90 days/65 cells no points
    If WorksheetFunction.CountA(Cell.Offset(, -64).Resize(, 65)) = 0 Then Reward.
    Last edited by SamT; 09-19-2017 at 03:01 PM.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    Excuse my ignorance. I need help getting this to work correctly.

    I'm right clicking my workbook, insert>module, then what? I tried a few methods I found online but still receiving errors when I try to run the module.

    I'm assuming something I have entered is wrong or do these need to be in two separate modules?
    Sub Reward()
    
    If Points > 1 Then
        Points = Points - 1
    ElseIf Points <= 1 Then Points = 0
    
    
    If WorksheetFunction.CountA(Cell.Offset(, -64).Resize(, 65)) = 0 Then Reward
    
    
    End Sub

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I would probably put that in the UserForm Module, since I suspect that the Value of "Points" comes from there.

    Any reference to a Cell or Range must also include a reference to the Worksheet.

    For Example. Note Cel is a Variable, but Cells() is an Excel Range Keyword.
    Set Cel = Sheets("Sheet1").Cells(Rows.Count, "D").End(xlUp)
    If WorksheetFunction.CountA(Cel.Offset(, -64).Resize(, 65)) = 0 Then Cel.Value = Reward
    Once a Range Variable is Set to a particular Range, the Sheet reference is inherent in the Variable.

    BTW, Your attachment didn't. Attach, that is.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    In line two of my original post is the attachment. It works for me?

    I'm getting a bit lost when it comes to setting a range variable. I'm following a few guides after googling setting range variables vba excel with no luck.
    2017-09-20_16-03-12.jpg
    Quote Originally Posted by SamT View Post
    I would probably put that in the UserForm Module, since I suspect that the Value of "Points" comes from there.

    Any reference to a Cell or Range must also include a reference to the Worksheet.

    For Example. Note Cel is a Variable, but Cells() is an Excel Range Keyword.
    Set Cel = Sheets("Sheet1").Cells(Rows.Count, "D").End(xlUp)
    If WorksheetFunction.CountA(Cel.Offset(, -64).Resize(, 65)) = 0 Then Cel.Value = Reward
    Once a Range Variable is Set to a particular Range, the Sheet reference is inherent in the Variable.

    BTW, Your attachment didn't. Attach, that is.
    Attached Files Attached Files
    Last edited by formulation; 09-20-2017 at 01:25 PM.

  6. #6
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Line 2???
    I was looking at this line
    . Attached Files
    OK, I DLed it... Checking
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  7. #7
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    For Example. Note Cel is a Variable, but Cells() is an Excel Range Keyword.
     	Set Cel = Sheets("Sheet1").Cells(Rows.Count, "D").End(xlUp) 
    If WorksheetFunction.CountA(Cel.Offset(, -64).Resize(, 65)) = 0 Then Cel.Value = Reward
    My bad. that was not a well thought out example. After setting Cel to a Cell in Column D, the next line tries to reference the Cell 64 columns to the left of D

    My Excel XP can't use the Functions in your attachment, so I can't see what your Worksheet is doing. I'm pretty sure I see what you want it to do, But I don't see how you are trying to do it.

    Don't go away, Several experts are watching. One will be here shortly.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  8. #8
    Updated sample data (Referenced cell data was incorrect)Copy of Legrand Attendance99999.xlsm

  9. #9
    Anyone have an idea? If you have time to figure it out use the sample data in post #8 above this.
    Thanks in advance!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •