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.

Dates

15-Jun-18

15-Mar-18

15-Feb-18

5-Oct-17

<colgroup><col></colgroup><tbody>
</tbody>

Range for date 1	Range for date 2	Range for date 3	Range for date 4
20-May-18	31-May-18	20-May-18	16-Apr-18
18-Apr-18	28-Feb-18	18-Feb-18	16-Jan-18
19-Mar-18	30-Nov-17	20-Nov-17	16-Oct-17
18-Feb-18	31-Aug-17	20-Aug-17	17-Jul-17
21-Jan-18	31-May-17	18-May-17	18-Apr-17
17-Dec-17	28-Feb-17	16-Feb-17	17-Jan-17
	30-Nov-16		17-Oct-16
	31-Aug-16		15-Jul-16
	31-May-16

<colgroup><col span="4"></colgroup><tbody>
</tbody>

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