VBA loop to find gaps in an array of dates

[Magoosball]

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 Date	Gaps
Employee 1
	04/01/2001	10/01/2001
	10/01/2001	11/1/2001
	12/1/2001	03/01/2002	Gap: 03/01/2002 to 02/12/2003
	02/12/2003	05/12/2005
	03/18/2003	01/01/2004
	11/12/2004	01/31/2005
Employee 2
	02/01/2006	05/01/2006	Gap: 05/01/2006 to 06/26/2006
	06/26/2006	09/30/2006	Gap: 09/30/2006 to 01/02/2007
	01/02/2007	03/16/2010	Gap: 03/16/2010 to 05/03/2010
	05/01/2007	04/03/2008
	07/01/2007	02/22/2007
	05/03/2010	07/31/2010
	08/20/2010	05/30/2011
	06/05/2011	06/06/2015
	06/28/2015	11/20/2017
Employee 3
	04/08/2013	07/27/2013
	08/20/2013	11/24/2017
Employee 4	12/01/1995	08/31/2001	Gap: 08/31/2001 to 10/30/2001
	10/30/2001	07/17/2014
	07/18/2014	11/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!

 

[Peter_SSs]

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.

Book1
	A	B	C	D	E
1		From Date	To Date		Gaps
2	Employee 1			2
3		1/04/2001	1/10/2001
4		1/10/2001	1/11/2001
5		1/12/2001	1/03/2002		Gap: 03/01/2002 to 02/12/2003
6		12/02/2003	12/05/2005
7		18/03/2003	1/01/2004
8		12/11/2004	31/01/2005
9	Employee 2			9
10		1/02/2006	1/05/2006		Gap: 05/01/2006 to 06/26/2006
11		26/06/2006	30/09/2006		Gap: 09/30/2006 to 01/02/2007
12		2/01/2007	16/03/2010		Gap: 03/16/2010 to 05/03/2010
13		1/05/2007	3/04/2008
14		1/07/2007	22/02/2007
15		3/05/2010	31/07/2010
16		20/08/2010	30/05/2011
17		5/06/2011	6/06/2015
18		28/06/2015	20/11/2017
19	Employee 3			19
20		8/04/2013	27/07/2013
21		20/08/2013	24/11/2017
22				22
23				23
24	Employee 4	1/12/1995	31/08/2001		Gap: 08/31/2001 to 10/30/2001
25		30/10/2001	17/07/2014
26		18/07/2014	26/11/2017
27				27
28				28
Gaps

Cell Formulas
Range	Formula
D2	=IF(B2="",ROW(),"")
E2	{=IF(D2="",IF(C2=MAX(INDEX(C:C,MAX(D1:D$2)+1):INDEX(C:C,MIN(D2:D$1000)-1)),"",IF(SUMPRODUCT((C2+30>=INDEX(B:B,MAX(D1:D$2)+1):INDEX(B:B,MIN(D2:D$1000)-1)) *(C2C:C,MAX(D1:D$2)+1):INDEX(C:C,MIN(D2:D$1000)-1)))=0,"Gap: " & TEXT(C2,"MM/DD/YYYY") & " to " & TEXT(MIN(IF(INDEX(B:B,MAX(D1:D$2)+1):INDEX(B:B,MIN(D2:D$1000)-1)>C2,INDEX(B:B,MAX(D1:D$2)+1):INDEX(B:B,MIN(D2:D$1000)-1))),"MM/DD/YYYY"),"")),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

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]

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]

Cheers. Glad to help.