Chronological insert line based on userform date

[Ekemas]

Hello All,

I'm constructing a planning file for work.
The idea would be to be able to insert a line of information based on the start date given at a userform.

As an example, a task can be added by using this form:

Then on the list highlighted in red, it should insert the line of information below the line which has the same start date or the start date which is earlier (but closest!) to the inserted start date.
To give an example below if the line which has to be inserted has a starting date of 23/03/2021 --> it has to be inserted just below line 10.
If the line which has to be inserted has a starting date of 17/04/2021 for example --> it has to be inserted just below line 11.

For me hardest part which I don't know really how to start, would be inserting the line at the right chronological place as mentioned above.
I would assume I would have to perform a loop, compare the difference between start date from line to be inserted with the dates in column G.
And where the difference would be the smallest or 0, I need to insert the line.

Filling the information afterwards based on userform I should be able to manage.

Hopefully someone can help me out with that first part...

Thanks in advance,
Sam

 

[Ekemas]

It might not be the cleanest way, but this is what I made:

Private Sub CommandButton1_Click()

'On Error GoTo ErrHandler

 Dim x As Integer
 Dim smallestDiff As Long
 Dim diff As Long
 Dim insertRow As Integer
 Dim insertDate As Date
 Dim comparedDate As Date
 
 insertDate = TextBox3.Value
 smallestDiff = 1000000
 
      Application.ScreenUpdating = False
      NumRows = Range("G9", Range("A1").End(xlDown)).Rows.Count
      
      insertRow = NumRows + 8
      
      For x = 9 To NumRows + 8
      
      comparedDate = Cells(x, 7).Value
      diff = insertDate - comparedDate
         If diff >= 0 And diff < smallestDiff Then
            smallestDiff = diff
            insertRow = x
         End If
        
      Next x

Unload Me

Exit Sub

'ErrHandler:
'MsgBox "An error occurred, did you fill everything in?"
Unload Me
Exit Sub

End Sub