Request VBA Solution: No 7 consecutive working dates

clovischunws

New Member
Joined
Jan 18, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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.
Example.PNG


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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,917
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.
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,241
Messages
5,600,499
Members
414,383
Latest member
konmtu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top