VBA loop to find gaps in an array of dates

Magoosball

Board Regular
Joined
Jun 4, 2017
Messages
55
I am trying to move an array based formula from a worksheet into a for loop in VBA.
I currently have a list of from dates in column A and to dates in column B. These dates represent where an employee lived during a period of time. I have a formula in column C that shows the dates if there is a gap of 30 days or more where the employee doesn't have a location where he / she lived. Here is the formula:

{=IF(C2=MAX(C$2:C$99),"",IF(SUMPRODUCT((C2+30>=B$2:B$99)*(C2< C$2:C$99))=0,"Gap: " & TEXT(C2,"MM/DD/YYYY") & " to " & TEXT(MIN(IF(B$2:B$99>C2,B$2:B$99)),"MM/DD/YYYY"),""))}

This formula works perfectly in Excel. I am trying to move this to VBA because my database reports multiple employees on the same spreadsheet separated by empty cells in column B and C. For example I will have the dates employee a lived from rows 2 to 12. B from rows 14 to 35 and so on. Of course these vary based off how many locations they have lived. Below is an example of what I am looking for the script to do:

From Date
To DateGaps
Employee 1
04/01/200110/01/2001
10/01/200111/1/2001
12/1/200103/01/2002Gap: 03/01/2002 to 02/12/2003
02/12/200305/12/2005
03/18/200301/01/2004
11/12/200401/31/2005
Employee 2
02/01/200605/01/2006Gap: 05/01/2006 to 06/26/2006
06/26/200609/30/2006Gap: 09/30/2006 to 01/02/2007
01/02/200703/16/2010Gap: 03/16/2010 to 05/03/2010
05/01/200704/03/2008
07/01/200702/22/2007
05/03/201007/31/2010
08/20/201005/30/2011
06/05/201106/06/2015
06/28/201511/20/2017
Employee 3
04/08/201307/27/2013
08/20/201311/24/2017
Employee 412/01/199508/31/2001Gap: 08/31/2001 to 10/30/2001
10/30/200107/17/2014
07/18/201411/26/2017

<tbody>
</tbody>

So I will have to have one big loop referencing the last row in the worksheet and then a smaller loop stopping during blank cells.
Help is greatly appreciated! Thank you in advance!
 
Last edited by a moderator:

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,471
Office Version
  1. 365
Platform
  1. Windows
You could still do it with worksheet formulas if you want. Here is your original formula (see note at the end of my post) adapted to work with a helper column, which could be hidden once populated.

Note that my dates in columns B:C are in d/mm/yyyy format.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">From Date</td><td style=";">To Date</td><td style="text-align: center;;"></td><td style=";">Gaps</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Employee 1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">2</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;">1/04/2001</td><td style="text-align: right;;">1/10/2001</td><td style="text-align: center;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;">1/10/2001</td><td style="text-align: right;;">1/11/2001</td><td style="text-align: center;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;">1/12/2001</td><td style="text-align: right;;">1/03/2002</td><td style="text-align: center;;"></td><td style=";">Gap: 03/01/2002 to 02/12/2003</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;">12/02/2003</td><td style="text-align: right;;">12/05/2005</td><td style="text-align: center;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;">18/03/2003</td><td style="text-align: right;;">1/01/2004</td><td style="text-align: center;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;">12/11/2004</td><td style="text-align: right;;">31/01/2005</td><td style="text-align: center;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Employee 2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">9</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;">1/02/2006</td><td style="text-align: right;;">1/05/2006</td><td style="text-align: center;;"></td><td style=";">Gap: 05/01/2006 to 06/26/2006</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;">26/06/2006</td><td style="text-align: right;;">30/09/2006</td><td style="text-align: center;;"></td><td style=";">Gap: 09/30/2006 to 01/02/2007</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;">2/01/2007</td><td style="text-align: right;;">16/03/2010</td><td style="text-align: center;;"></td><td style=";">Gap: 03/16/2010 to 05/03/2010</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;">1/05/2007</td><td style="text-align: right;;">3/04/2008</td><td style="text-align: center;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;">1/07/2007</td><td style="text-align: right;;">22/02/2007</td><td style="text-align: center;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;">3/05/2010</td><td style="text-align: right;;">31/07/2010</td><td style="text-align: center;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;">20/08/2010</td><td style="text-align: right;;">30/05/2011</td><td style="text-align: center;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;">5/06/2011</td><td style="text-align: right;;">6/06/2015</td><td style="text-align: center;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;">28/06/2015</td><td style="text-align: right;;">20/11/2017</td><td style="text-align: center;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">Employee 3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">19</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style="text-align: right;;"></td><td style="text-align: right;;">8/04/2013</td><td style="text-align: right;;">27/07/2013</td><td style="text-align: center;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="text-align: right;;"></td><td style="text-align: right;;">20/08/2013</td><td style="text-align: right;;">24/11/2017</td><td style="text-align: center;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">22</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">23</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style=";">Employee 4</td><td style="text-align: right;;">1/12/1995</td><td style="text-align: right;;">31/08/2001</td><td style="text-align: center;;"></td><td style=";">Gap: 08/31/2001 to 10/30/2001</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style="text-align: right;;"></td><td style="text-align: right;;">30/10/2001</td><td style="text-align: right;;">17/07/2014</td><td style="text-align: center;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">26</td><td style="text-align: right;;"></td><td style="text-align: right;;">18/07/2014</td><td style="text-align: right;;">26/11/2017</td><td style="text-align: center;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">27</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">27</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">28</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">28</td><td style=";"></td></tr></tbody></table><p style="width:3.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Gaps</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=IF(<font color="Blue">B2="",ROW(<font color="Red"></font>),""</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">{=IF(<font color="Blue">D2="",IF(<font color="Red">C2=MAX(<font color="Green">INDEX(<font color="Purple">C:C,MAX(<font color="Teal">D1:D$2</font>)+1</font>):INDEX(<font color="Purple">C:C,MIN(<font color="Teal">D2:D$1000</font>)-1</font>)</font>),"",IF(<font color="Green">SUMPRODUCT(<font color="Purple">(<font color="Teal">C2+30>=INDEX(<font color="#FF00FF">B:B,MAX(<font color="Navy">D1:D$2</font>)+1</font>):INDEX(<font color="#FF00FF">B:B,MIN(<font color="Navy">D2:D$1000</font>)-1</font>)</font>)
*(<font color="Teal">C2<INDEX(<font color="#FF00FF">C:C,MAX(<font color="Navy">D1:D$2</font>)+1</font>):INDEX(<font color="#FF00FF">C:C,MIN(<font color="Navy">D2:D$1000</font>)-1</font>)</font>)</font>)=0,"Gap: " & TEXT(<font color="Purple">C2,"MM/DD/YYYY"</font>) & " to "
& TEXT(<font color="Purple">MIN(<font color="Teal">IF(<font color="#FF00FF">INDEX(<font color="Navy">B:B,MAX(<font color="Blue">D1:D$2</font>)+1</font>):INDEX(<font color="Navy">B:B,MIN(<font color="Blue">D2:D$1000</font>)-1</font>)>C2,INDEX(<font color="Navy">B:B,MAX(<font color="Blue">D1:D$2</font>)+1</font>):INDEX(<font color="Navy">B:B,MIN(<font color="Blue">D2:D$1000</font>)-1</font>)</font>)</font>),"MM/DD/YYYY"</font>),""</font>)</font>),""</font>)}</td></tr></tbody></table><b>Enter without the {} but confirm with Ctrl+Shift+Enter, not just Enter</b>
<b>If entered correctly, Excel will insert the {}</b></td></tr></table><br />


N.B.
If you try to post a formula that contains a < symbol followed immediately by a letter, the forum software misinterprets your formula and truncates it. To avoid that, add a space between those two characters. I have fixed your post above for that problem.,
 

Magoosball

Board Regular
Joined
Jun 4, 2017
Messages
55
Hi Peter,
Sorry for the delayed response.

The idea to add a helper column was absolutely brilliant! This worked perfectly. I might have to use the helper column idea in other reports that I have as well.
Also read through your signature and will make sure my posts look nicer in the future.

Thank you so much for the help!!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,471
Office Version
  1. 365
Platform
  1. Windows
Cheers. Glad to help. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,349
Messages
5,635,768
Members
416,879
Latest member
Excel_Newbie4980

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