Request VBA Solution: No 7 consecutive working dates

[clovischunws]

Hi All,

I have a difficult question on hand. Please see the attached excel example.

Background
In the attached excel, it shows 31 days in Jan 2020. The numbers (B2:AF4) represent the number of dutuies in each calender date.

Rules
According to company rules:
1.a person can have more than 1 duties on the same day
2.a person cannot work for 7 consecutive days

Questions

What excel function/VBA code can help to attain the below listed results? I willl use 1 person (Anna) to illustrate:

- Check the values from 1 Jan to 7 Jan (B2:H2)
- if there is one cell's value is not equal to to 0, then pop up message box "7 consecutive working days"
- if is at lease one cell's value equal to 0, then continue to check the values from 2 Jan to 8 Jan (C2:I2)
- The last checking process should end after checking the cell's value from 25 Jan to 31 Jan


Thanks for your input

 

[mikerickson]

Actualy, looking at Peter_SSs answer gave me the idea for a perhaps cleaner user interface.

Select B2 and set Conditional Formatting with the formula =(COUNTIF(B2:H2,0)=0). Then copy that formatting to the rest of the cells.

 

[Peter_SSs]

Many thanks for your help. It works.

You are welcome.

If I add I more staff, let's say David underneath Chris, how to amend your provide VBA code to cope with the case?

No amendment required at all (provided there are no completely empty rows in the data. That is, add David & his data on the nest row and just run the code again).

can you teach me how to understand your VBA's logic behind?

Sure ..
1. Read all the data into an array in memory, but think of it just like looking at the sheet.
2. Work across row 2, starting with a count of 0 & starting at column B. For each cell if you encounter anything other than 0, add 1 to the count. If you encounter a 0, reset the count to 0. If the count ever gets to 7, record that name in column A of that row, reset the count to 0 and immediately start the next row.
3. Repeat step 2 until no more rows.
4. If any names were recorded, show them.

Actualy, looking at Peter_SSs answer gave me the idea for a perhaps cleaner user interface.

Select B2 and set Conditional Formatting with the formula =(COUNTIF(B2:H2,0)=0). Then copy that formatting to the rest of the cells.

The user did specifically ask for a message box, but in any case either your formula needs amending or clearer direction on restricting what cells the CF is copied to otherwise anybody with 6 or less consecutive non-zero values at the right end of their row will incorrectly show highlighted.