VBA - Help - Idea Needed to Insert Rows if the next date is not consecutive.

[Trying2learnVBA]

Hello,
I am not quite sure how to properly phrase my question.
I am trying to code a macro to insert rows if the date is not the next day.
But I am trying to keep repeated dates.
The highlighted dates came from my data set. The none highlighted dates is where I need rows to be inserted as needed.

Book3
	B								J	K	L
22	Days with Activity								Date	Result Needed
23	1								08/01/22	08/01/22
24	1								08/01/22	08/01/22
25	1								08/01/22	08/01/22
26	1								08/01/22	08/01/22
27	1								08/01/22	08/01/22
28	5								8/5/2022	8/2/2022	VBA to insert rows if the date is not consecutive
29	5								8/5/2022	8/3/2022	Or VBA to insert rows if the Days are not consecutive
30	8								8/8/2022	8/4/2022
31	8								8/8/2022	8/5/2022
32	8								8/8/2022	8/6/2022
33	8								8/8/2022	8/7/2022
34	12								8/12/2022	8/8/2022
35	12								8/12/2022	8/8/2022
36	12								8/12/2022	8/8/2022
37	15								8/15/2022	8/8/2022
38	15								8/15/2022	8/9/2022
39	17								8/17/2022	8/10/2022
40	17								8/17/2022	8/11/2022
41	19								8/19/2022	8/12/2022
42	22								8/22/2022	8/12/2022
43	22								8/22/2022	8/12/2022
44	24								8/24/2022	8/13/2022
45	24								8/24/2022	8/14/2022
46	26								8/26/2022	8/15/2022
47	26								8/26/2022	8/15/2022
48	30								8/30/2022	8/16/2022
49	30								8/30/2022	8/17/2022
50	31								8/31/2022	8/17/2022
51	31								8/31/2022	8/18/2022
52										8/19/2022
53										8/20/2022
54										8/21/2022
55										8/22/2022
56										8/22/2022
57										8/23/2022
58										8/24/2022
59										8/24/2022
60										8/25/2022
61										8/26/2022
62										8/26/2022
63										8/27/2022
64										8/28/2022
65										8/29/2022
66										8/30/2022
67										8/30/2022
68										8/31/2022
69										8/31/2022
Advances.

Here - i am trying to play using the mid formula to extract the date. But I simply want the proper number of rows to be inserted when the numbers or dates are not consecutive. But I do want to keep repeated dates.

Please point me in the right direction.
I can't quite come up with the right logic for an If else statement.

Thank you in advance gurus!!

 

[Micron]

I used it on your post 1 data if that's what you're asking. I copied that, pasted in a sheet and a bunch of hidden columns appeared. Your dates were in col I so I used that. No idea why that would happen. Should work if you change I to whatever column you want to run it against. I'm guessing that is E.
Too late for me to experiment any more tonight - it's after midnight.

Note - do not change i, just I.

 

[Micron]

I changed I to E and used your data set from post 8. Seems to work.

 

[Trying2learnVBA]

I changed I to E and used your data set from post 8. Seems to work.
View attachment 75063

It was midnight for me too. I couldn't sleep unable to get this to work.

It looks like it is working perfect for you. I can't figure out why it errors out for me. Run time error 13 - Type mismatch is the error.
The debugger highlights this line "If rng.Offset(1, 0) - rng > 1 Then"

Sub insertDates()
Dim rng As Range
Dim i As Integer, Lrow As Integer

Lrow = Cells(Rows.Count, "E").End(xlUp).Row
For Each rng In Range("E2:E" & Lrow)
  If rng.Offset(1, 0) - rng > 1 Then
    For i = 1 To (rng.Offset(1, 0) - rng) - 1
       rng.Offset(i, 0).EntireRow.Insert Shift:=xlDown
       rng.Offset(i, 0) = rng + i
     Next
  End If
Next

End Sub

rng.Offset(1,0) shows the correct date 8/1/22 but the "-rang>1 part says ".Advance Amount" does this mean it is looking at the cell above?

 

[Trying2learnVBA]

I changed I to E and used your data set from post 8. Seems to work.
View attachment 75063

Ok I figured it out - I had to change the range to E3:E because my data starts on row three I guess.
Thank you so much dude!! This is awesome.

 

[Micron]

That you figured that out is great news! You're on your way to adapting vba code.