closest two dates macro

[xelhelp]

hi,

i have sheet1 that has a list of dates in column J, starting at J2 down to J42.
in sheet two i have another list of dates in column E starting at E5 down to EX, X because these dates are being populated from an external feed and I don't know how far down the rows it will be populated (i.e. it changes), but the list of dates will always start at cell E5

i would like a macro that will check the dates in each cell of column E starting at cell E5 down to EX with the dates in cells J2:J42 of sheet1,

i would like the date from cell J2:J42 of sheet1 that is on or before the date in column E to be populated in column I on the same row as the cell in column E, and in the same row of column J i would like the date from cell J2:J42 of sheet1 that is on or after the date in column E

so cells J2:J42 of sheet1 might look like this:

<table border="0" cellpadding="0" cellspacing="0" width="82"><col style="width: 62pt;" width="82"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt; width: 62pt;" width="82" height="20">3/16/2011</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">6/15/2011</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">9/21/2011</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">12/21/2011</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">3/21/2012</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">6/20/2012</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">9/19/2012</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">12/19/2012</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">3/20/2013</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">6/19/2013</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">9/18/2013</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">12/18/2013</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">3/19/2014</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">6/18/2014</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">9/17/2014</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">12/17/2014</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">3/18/2015</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">6/17/2015</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">9/16/2015</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">12/16/2015</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">3/16/2016</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">6/15/2016</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">9/21/2016</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">12/21/2016</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">3/15/2017</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">6/21/2017</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">9/20/2017</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">12/20/2017</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">3/21/2018</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">6/20/2018</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">9/19/2018</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">12/19/2018</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">3/20/2019</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">6/19/2019</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">9/18/2019</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">12/18/2019</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">3/18/2020</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">6/17/2020</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">9/16/2020</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">12/16/2020</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">3/17/2021</td> </tr> </tbody></table>
and in this particular scenario lets assume that column E in sheet2 is populated from E5 to E8 that has the following dates

<table border="0" cellpadding="0" cellspacing="0" width="76"><col style="width: 57pt;" width="76"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt; width: 57pt;" width="76" height="20">8/31/2011</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">2/29/2012</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">8/31/2012</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">2/28/2013</td> </tr> </tbody></table>

based on this information cells I5:I8 of sheet2 would look like this:

<table border="0" cellpadding="0" cellspacing="0" width="75"><col style="width: 56pt;" width="75"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; width: 56pt;" width="75" height="20">6/15/2011</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">12/21/2011</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">6/20/2012</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">12/19/2012</td> </tr> </tbody></table>

and cells J5:J8 of sheet2 would look like this:

<table border="0" cellpadding="0" cellspacing="0" width="68"><col style="width: 51pt;" width="68"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; width: 51pt;" width="68" height="20">9/21/2011</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">3/21/2012</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">9/19/2012</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">3/20/2013</td> </tr> </tbody></table>
i need to use the worksheet change event handler so the dates are updated every time the external feed populates an updated list of dates in column E of sheet2

i think i have the beginning and end of the code, here's what i have so far
any help would be appreciated!

Private Sub Worksheet_Change(ByVal Target As Range)

Dim RowCount As Double
RowCount = Application.WorksheetFunction.CountA(Range("E5:E200"))

Dim Counter As Double
Counter = 0

Application.EnableEvents = False

Do Until Counter > RowCount - 1

'date lookup code goes here
' for referencing the first cell in column E which is E5 i would use the following code Range("E5").Offset(Counter, 0)

' for referencing the first cell in column I which is I5 i would use the following code Range("E5").Offset(Counter, 4)

' for referencing the first cell in column J which is J5 i would use the following code Range("E5").Offset(Counter, 5)

Counter = Counter + 1
Loop

Application.EnableEvents = True


End Sub

 

[tusharm]

No need to solve this with VBA. If you must, use VBA to add the formulas to columns I and J.

In I5 enter the formula =INDEX(Sheet1!$I$2:$I$42,MATCH(Sheet2!E5,Sheet1!$I$2:$I$42,1))

In J5 enter the array formula =MIN(IF(E5<=Sheet1!$I$2:$I$42,Sheet1!$I$2:$I$42))

Copy I5:J5 as far down as you have data.

To maintain symmetry, you may want to use, for I5, the array formula =MAX(IF(E5>=Sheet1!$I$2:$I$42,Sheet1!$I$2:$I$42))

To enter an array formula complete data entry with the CTRL+SHIFT+ENTER key combination rather than just the ENTER or TAB key. If done correctly, *Excel* will show the formula enclosed in curly brackets { and }

hi,

i have sheet1 that has a list of dates in column J, starting at J2 down to J42.
in sheet two i have another list of dates in column E starting at E5 down to EX, X because these dates are being populated from an external feed and I don't know how far down the rows it will be populated (i.e. it changes), but the list of dates will always start at cell E5

i would like a macro that will check the dates in each cell of column E starting at cell E5 down to EX with the dates in cells J2:J42 of sheet1,

i would like the date from cell J2:J42 of sheet1 that is on or before the date in column E to be populated in column I on the same row as the cell in column E, and in the same row of column J i would like the date from cell J2:J42 of sheet1 that is on or after the date in column E

so cells J2:J42 of sheet1 might look like this:

<table border="0" cellpadding="0" cellspacing="0" width="82"><col style="width: 62pt;" width="82"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt; width: 62pt;" width="82" height="20">3/16/2011</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">6/15/2011</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">9/21/2011</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">12/21/2011</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">3/21/2012</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">6/20/2012</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">9/19/2012</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">12/19/2012</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">3/20/2013</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">6/19/2013</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">9/18/2013</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">12/18/2013</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">3/19/2014</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">6/18/2014</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">9/17/2014</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">12/17/2014</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">3/18/2015</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">6/17/2015</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">9/16/2015</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">12/16/2015</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">3/16/2016</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">6/15/2016</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">9/21/2016</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">12/21/2016</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">3/15/2017</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">6/21/2017</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">9/20/2017</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">12/20/2017</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">3/21/2018</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">6/20/2018</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">9/19/2018</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">12/19/2018</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">3/20/2019</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">6/19/2019</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">9/18/2019</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">12/18/2019</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">3/18/2020</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">6/17/2020</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">9/16/2020</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">12/16/2020</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">3/17/2021</td> </tr> </tbody></table>
and in this particular scenario lets assume that column E in sheet2 is populated from E5 to E8 that has the following dates

<table border="0" cellpadding="0" cellspacing="0" width="76"><col style="width: 57pt;" width="76"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt; width: 57pt;" width="76" height="20">8/31/2011</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">2/29/2012</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">8/31/2012</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">2/28/2013</td> </tr> </tbody></table>

based on this information cells I5:I8 of sheet2 would look like this:

<table border="0" cellpadding="0" cellspacing="0" width="75"><col style="width: 56pt;" width="75"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; width: 56pt;" width="75" height="20">6/15/2011</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">12/21/2011</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">6/20/2012</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">12/19/2012</td> </tr> </tbody></table>

and cells J5:J8 of sheet2 would look like this:

<table border="0" cellpadding="0" cellspacing="0" width="68"><col style="width: 51pt;" width="68"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; width: 51pt;" width="68" height="20">9/21/2011</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">3/21/2012</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">9/19/2012</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">3/20/2013</td> </tr> </tbody></table>
i need to use the worksheet change event handler so the dates are updated every time the external feed populates an updated list of dates in column E of sheet2

i think i have the beginning and end of the code, here's what i have so far
any help would be appreciated!

Private Sub Worksheet_Change(ByVal Target As Range)

Dim RowCount As Double
RowCount = Application.WorksheetFunction.CountA(Range("E5:E200"))

Dim Counter As Double
Counter = 0

Application.EnableEvents = False

Do Until Counter > RowCount - 1

'date lookup code goes here
' for referencing the first cell in column E which is E5 i would use the following code Range("E5").Offset(Counter, 0)

' for referencing the first cell in column I which is I5 i would use the following code Range("E5").Offset(Counter, 4)

' for referencing the first cell in column J which is J5 i would use the following code Range("E5").Offset(Counter, 5)

Counter = Counter + 1
Loop

Application.EnableEvents = True


End Sub