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:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]From Date
[/TD]
[TD]To Date[/TD]
[TD]Gaps[/TD]
[/TR]
[TR]
[TD]Employee 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]04/01/2001[/TD]
[TD]10/01/2001
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10/01/2001[/TD]
[TD]11/1/2001[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12/1/2001[/TD]
[TD]03/01/2002[/TD]
[TD]Gap: 03/01/2002 to 02/12/2003[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]02/12/2003[/TD]
[TD]05/12/2005[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]03/18/2003[/TD]
[TD]01/01/2004[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]11/12/2004[/TD]
[TD]01/31/2005[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]02/01/2006[/TD]
[TD]05/01/2006[/TD]
[TD]Gap: 05/01/2006 to 06/26/2006[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]06/26/2006[/TD]
[TD]09/30/2006[/TD]
[TD]Gap: 09/30/2006 to 01/02/2007[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]01/02/2007[/TD]
[TD]03/16/2010[/TD]
[TD]Gap: 03/16/2010 to 05/03/2010[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]05/01/2007[/TD]
[TD]04/03/2008[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]07/01/2007[/TD]
[TD]02/22/2007[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]05/03/2010[/TD]
[TD]07/31/2010[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]08/20/2010[/TD]
[TD]05/30/2011
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]06/05/2011[/TD]
[TD]06/06/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]06/28/2015[/TD]
[TD]11/20/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]04/08/2013[/TD]
[TD]07/27/2013[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]08/20/2013[/TD]
[TD]11/24/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee 4[/TD]
[TD]12/01/1995[/TD]
[TD]08/31/2001[/TD]
[TD]Gap: 08/31/2001 to 10/30/2001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10/30/2001[/TD]
[TD]07/17/2014[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]07/18/2014[/TD]
[TD]11/26/2017[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.