Macro to find the closest date and return the cell.address (inferior date)

[QcHuldar]

Hi everybody!

Second post here, i think i need some help..

I have a sheet that many users use to create TASK for vehicles. Those task should be created in order of "Starting date of the task".

I've been able to find and create the task template under a task with the SAME date, i can't find a way to select the closest inferior date…

Here's the code i use to find the SAME date:

Dim findDate As Date
Dim findStr As String
Dim R As Range, WS As Worksheet

Set findDate = TxtDateDebut 'This is from a TextBox in a UserForm
Set WS = Worksheets("TÂCHES")

findStr = Format(TxtDateDebut, "DD/MM/YYYY")

MsgBox TxtDateDebut

With WS

Set R = .Cells.Find(what:=findStr, LookIn:=xlValues, lookat:=xlWhole, SearchDirection:=xlPrevious)

If Not R Is Nothing Then

ActiveSheet.Range(R.Address).Select
ActiveCell.Offset(1, 0).Resize(AddTemplate.TxtNombre.Value + 3).EntireRow.Insert


End With

Tâches.png

Let's say a user create a task that would start the 29/10/2020, i need to create that task in between the task that start the 28/10/2020 and the one that start the 02/11/2020.

Anybody has an idea to get the immediate inferior date (if the exact one doesn't exist) ?

Set R = .Cells.Find(what:=(<=)findStr, LookIn:=xlValues, lookat:=xlWhole, SearchDirection:=xlPrevious)

[alansidman]

Administrative Note:

Welcome to the forum.

Unfortunately, this is a duplicate thread, and you are allowed only ONE thread per issue here.

Please see Forum Rule #5 about thread duplication.

I am closing this thread, but you may continue here in the original thread