VBA code to find the closest earlier date from a range

[kirtesh250187]

Hi,

Is there a way from which we can find the closest earlier date to a date from a range. My range for each date will change every time. I want to find the closest earlier date to the one in Dates column below.

[TABLE="width: 118"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Dates[/TD]
[/TR]
[TR]
[TD]15-Jun-18[/TD]
[/TR]
[TR]
[TD]15-Mar-18[/TD]
[/TR]
[TR]
[TD]15-Feb-18[/TD]
[/TR]
[TR]
[TD]5-Oct-17[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 304"]
<colgroup><col span="4"></colgroup><tbody>[TR]
[TD]Range for date 1[/TD]
[TD]Range for date 2[/TD]
[TD]Range for date 3[/TD]
[TD]Range for date 4[/TD]
[/TR]
[TR]
[TD]20-May-18[/TD]
[TD]31-May-18[/TD]
[TD]20-May-18[/TD]
[TD]16-Apr-18[/TD]
[/TR]
[TR]
[TD]18-Apr-18[/TD]
[TD]28-Feb-18[/TD]
[TD]18-Feb-18[/TD]
[TD]16-Jan-18[/TD]
[/TR]
[TR]
[TD]19-Mar-18[/TD]
[TD]30-Nov-17[/TD]
[TD]20-Nov-17[/TD]
[TD]16-Oct-17[/TD]
[/TR]
[TR]
[TD]18-Feb-18[/TD]
[TD]31-Aug-17[/TD]
[TD]20-Aug-17[/TD]
[TD]17-Jul-17[/TD]
[/TR]
[TR]
[TD]21-Jan-18[/TD]
[TD]31-May-17[/TD]
[TD]18-May-17[/TD]
[TD]18-Apr-17[/TD]
[/TR]
[TR]
[TD]17-Dec-17[/TD]
[TD]28-Feb-17[/TD]
[TD]16-Feb-17[/TD]
[TD]17-Jan-17[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]30-Nov-16[/TD]
[TD] [/TD]
[TD]17-Oct-16[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]31-Aug-16[/TD]
[TD] [/TD]
[TD]15-Jul-16[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]31-May-16[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Thanks

 

[Peter_SSs]

Is this what you mean? Formula shown is copied down.

Excel Workbook
	A	B	C	D
1	Dates	Earlier Date
2	15-Jun-18	20-May-18
3	15-Mar-18	28-Feb-18
4	15-Feb-18	20-Nov-17
5	5-Oct-17	17-Jul-17
6
7	Range for date 1	Range for date 2	Range for date 3	Range for date 4
8	20-May-18	31-May-18	20-May-18	16-Apr-18
9	18-Apr-18	28-Feb-18	18-Feb-18	16-Jan-18
10	19-Mar-18	30-Nov-17	20-Nov-17	16-Oct-17
11	18-Feb-18	31-Aug-17	20-Aug-17	17-Jul-17
12	21-Jan-18	31-May-17	18-May-17	18-Apr-17
13	17-Dec-17	28-Feb-17	16-Feb-17	17-Jan-17
14		30-Nov-16		17-Oct-16
15		31-Aug-16		15-Jul-16
16		31-May-16
17
Earlier Date

Edit: I forgot the thread title said vba. Do you really need the vba?

 

[kirtesh250187]

Yes, this is what i am looking for but can we do it via macro because it wanted to make it a part of another larger macro. Thanks

 

[Peter_SSs]

For that layout, try

Sub EarlierDate()
  Dim lr As Long, c As Long
  Dim r As Range
  
  Const fr As Long = 8
  
  For Each r In Range("A2", Range("A1").End(xlDown))
    c = c + 1
    lr = Cells(Rows.Count, c).End(xlUp).Row
    r.Offset(, 1).Value = Evaluate(Replace(Replace(Replace( _
                          "aggregate(14,6,index($a$#:$d$%,0,rows(a$2:a@))/(index($a$#:$d$%,0,rows(a$2:a@))<a@),1)" _
                          , "#", fr), "%", lr), "@", r.Row))
  Next r
End Sub