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.
Attachment 20396
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.https://www.excelforum.com/images/misc/paperclip.png Attached Files

Rewarding

Code:

---

If Points > 1 then
Points = Points - 1
ElseIf Points <= 1 then Points = 0

---

90 days/65 cells no points

Code:

---

If WorksheetFunction.CountA(Cell.Offset(, -64).Resize(, 65)) = 0 Then Reward.

---

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?

Code:

---

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

---

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.

Code:

---

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.

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. :dunno
Attachment 20413

Quote:

---

Originally Posted by SamT

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.

Code:

---

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.

---

Quote:

---

Line 2???

---

I was looking at this line

Quote:

---

.https://www.excelforum.com/images/misc/paperclip.png Attached Files

---

OK, I DLed it... Checking

Quote:

---

For Example. Note Cel is a Variable, but Cells() is an Excel Range Keyword.

Code:

---

        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.

Updated sample data (Referenced cell data was incorrect)Attachment 20423

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