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
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

What, exactly, do you want as result(s) for each of those sample users & where would the result(s) go?

Can you give us the sample data again (perhaps even with the expected results entered manually) in a form that we can copy to test with (there is a lot of manual typing to set that up. ;)). My signature block below has help with that.
 

clovischunws

New Member
Joined
Jan 18, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hi Peter_SSs,

Thanks for your reply. I upload the example excel in the below link. Help it will be much clear.


My expected results are
1. Excel check from 1 Jan (B2) to 31 Jan (AF2)
2. Whenever there are 7 consecutive working days (e.g. for Anna, from 8 Jan to 14 Jan), a pop up message will show " 7 consecutive working days"

Thanks
 

clovischunws

New Member
Joined
Jan 18, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi Macropd,

Readt the cross-posting rules and thanks.

Yes, I have cross-posted my question to Excel Forum in order to draw more insights. Both threads contain the same details.

Thanks
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Whenever there are 7 consecutive working days (e.g. for Anna, from 8 Jan to 14 Jan), a pop up message will show " 7 consecutive working days"
Sorry that is still not clear. Remember that you are familiar with what you want but we are not. :)

For example,
1. Bob has 9 consecutive days (6-Jan to 14-Jan). Should this pop up 3 messages since 6-Jan to 12-Jan is 7 consecutive & 7-Jan to 13-Jan is 7 consecutive & 8-Jan to 14-Jan is 7 consecutive.

2. If Anna also had 7 consecutive from 21-Jan to 27-Jan as well as 8-Jan to 14-Jan, would that give 2 messages? or one message saying "7 consecutive twice" or something else?

3. For the "pop up message" do you actually want something like this?
1579412747552.png

Or do you want something written on her row on the sheet?
If an actual message box like the image above,
- do you want separate messages about each person or all combined into one message box?
- do you want the consecutive dates listed in the message(s)?

I have cross-posted my question to Excel Forum in order to draw more insights.
BTW, that may actually get you less responses. Many helpers will not want to go to the other forum to see what responses you have there and also do not want to risk wasting their time answering in case an answer has already been provided on the other forum. Worth considering for future questions. :)
 

clovischunws

New Member
Joined
Jan 18, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi Peter_SSs

Thanks for your help.Sorry that I am a newbie to excel forums so I may accidentically ignore some rules. I have closed the same post in another forum.

Here are the clarifications:

1. Bob has 9 consecutive days (6-Jan to 14-Jan). Should this pop up 3 messages since 6-Jan to 12-Jan is 7 consecutive & 7-Jan to 13-Jan is 7 consecutive & 8-Jan to 14-Jan is 7 consecutive. ---> In this case, I only want pop up message appears once. That means this is a 0 and 1 case. Whenever a person has 7 consecutive working days, there is 1 pop up message. No need to consider how many time the "7 consecutive working days" pattern have appreared for each person

2. 2. If Anna also had 7 consecutive from 21-Jan to 27-Jan as well as 8-Jan to 14-Jan, would that give 2 messages? or one message saying "7 consecutive twice" or something else?--> 1 pop up message is enough

3. For the "pop up message" do you actually want something like this? ---> Yes
1579412747552.png


Or do you want something written on her row on the sheet?---> No need to write anything on her sheet
If an actual message box like the image above,
- do you want separate messages about each person or all combined into one message box?---> Combine into 1 pop up message box please
- do you want the consecutive dates listed in the message(s)? ---> No need at this moment. However, if you can make this works, can you please also provide a solution for my future reference?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
See how this macro goes then.

VBA Code:
Sub Check_7()
  Dim a As Variant
  Dim sList As String
  Dim i As Long, j As Long, k As Long, uba2 As Long
 
  a = Range("A1").CurrentRegion.Value
  uba2 = UBound(a, 2)
  For i = 2 To UBound(a)
    k = 0
    For j = 2 To uba2
      If a(i, j) > 0 Then
        k = k + 1
        If k = 7 Then
          sList = sList & vbLf & a(i, 1)
          Exit For
        End If
      Else
        k = 0
      End If
    Next j
  Next i
  If Len(sList) > 0 Then MsgBox "The following have 7 consecutive:" & sList
End Sub

For the sample data I got this. The blue shading I did manually just so I could see what was being counted.

1579424675200.png
 

clovischunws

New Member
Joined
Jan 18, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hi Peter_SSs,

Many thanks for your help. It works. If I add I more staff, let's say David underneath Chris, how to amend your provide VBA code to cope with the case?
 

clovischunws

New Member
Joined
Jan 18, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hi Peter_SSs,

Many thanks for your help. It works. If I add I more staff, let's say David underneath Chris, how to amend your provide VBA code to cope with the case?
Actually, can you teach me how to understand your VBA's logic behind?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,240
Messages
5,600,491
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