Chronological insert line based on userform date

Ekemas

New Member
Joined
Dec 13, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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:

2021-03-24 15_03_33-New Task Entry.png


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.

2021-03-24 15_07_57-DT2_CAL_Resources_Stellantis - Copy 5  -  Repaired - Excel.png


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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Ekemas

New Member
Joined
Dec 13, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
It might not be the cleanest way, but this is what I made:

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,383
Messages
5,635,942
Members
416,889
Latest member
dhegs

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
Top