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

Trying2learnVBA

Board Regular
Joined
Aug 21, 2019
Messages
67
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
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
BJKL
22Days with ActivityDateResult Needed
23108/01/2208/01/22
24108/01/2208/01/22
25108/01/2208/01/22
26108/01/2208/01/22
27108/01/2208/01/22
2858/5/20228/2/2022VBA to insert rows if the date is not consecutive
2958/5/20228/3/2022Or VBA to insert rows if the Days are not consecutive
3088/8/20228/4/2022
3188/8/20228/5/2022
3288/8/20228/6/2022
3388/8/20228/7/2022
34128/12/20228/8/2022
35128/12/20228/8/2022
36128/12/20228/8/2022
37158/15/20228/8/2022
38158/15/20228/9/2022
39178/17/20228/10/2022
40178/17/20228/11/2022
41198/19/20228/12/2022
42228/22/20228/12/2022
43228/22/20228/12/2022
44248/24/20228/13/2022
45248/24/20228/14/2022
46268/26/20228/15/2022
47268/26/20228/15/2022
48308/30/20228/16/2022
49308/30/20228/17/2022
50318/31/20228/17/2022
51318/31/20228/18/2022
528/19/2022
538/20/2022
548/21/2022
558/22/2022
568/22/2022
578/23/2022
588/24/2022
598/24/2022
608/25/2022
618/26/2022
628/26/2022
638/27/2022
648/28/2022
658/29/2022
668/30/2022
678/30/2022
688/31/2022
698/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!!
 
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.
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
I changed I to E and used your data set from post 8. Seems to work.
1664461912724.png
 
Upvote 0
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"
VBA Code:
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?
 
Upvote 0
That you figured that out is great news! You're on your way to adapting vba code. (y)
 
Upvote 0

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

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
Back
Top