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

 

[Tinus Bergakker]

why not use a vlookup?

 

[xelhelp]

hi,
thanks for the quick response..
i don't think i can use vlookup because i want two values, one on or before my date in column E in sheet2 and one or after my date in column E in sheet2
also the dates i have in column E are not static...it is changing, so i don't know how many rows down column E will be populated

plus after columns I and J of sheet2 have been populated with dates, i would like to get other values from sheet1 that correspond to the the dates in column I and J of sheet2

so for example i might have the following values in cells L2:L42 in sheet1..these values correspond to the dates in cells J2:J42 of sheet1 and I would want the values that correspond to the dates populated in column I and J in sheet2 to be populated in column K and L of sheet2

so to clarify, column L is populated with values from L2:L42 in sheet1 that correspond to the dates in column I in sheet2

column K is populated with values from L2:L42 in sheet1 that correspond to the dates in column J in sheet2

hopefully this makes sense...thanks

<table border="0" cellpadding="0" cellspacing="0" width="93"><col style="width: 70pt;" width="93"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">1.000104949</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">1.000898123</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">1.001913061</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">1.003071732</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">1.004599396</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">1.006764238</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">1.009799003</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">1.013774596</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">1.018611498</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">1.024218176</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">1.030580633</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">1.03767296</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">1.045496078</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">1.054012442</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">1.063224291</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">1.073134797</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">1.083761588</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">1.095027817</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">1.106923241</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">1.119423556</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">1.13251778</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">1.146108701</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">1.161279519</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">1.175846722</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">1.189736412</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">1.206334887</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">1.222130502</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">1.238287407</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">1.254822241</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">1.271236885</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">1.288018889</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">1.305413137</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">1.32316603</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20"> <table border="0" cellpadding="0" cellspacing="0" width="94"><col style="width: 71pt;" width="94"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 71pt;" width="94" height="20">0.999895062</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 71pt;" width="94" height="20">0.999102683</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 71pt;" width="94" height="20">0.998090592</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 71pt;" width="94" height="20">0.996937675</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 71pt;" width="94" height="20">0.995421661</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 71pt;" width="94" height="20">0.993281209</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 71pt;" width="94" height="20">0.990296086</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 71pt;" width="94" height="20">0.986412566</td> </tr> </tbody></table></td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl63" style="height: 15pt; width: 70pt;" width="93" height="20">
</td></tr></tbody></table>

 

[Warship]

When you receive Sheet2 Col E updates:

Is it always appended to the bottom of E?
Is it always 1 date added or could multiple dates be added in 1 update

 

[xelhelp]

hi,

when the update happens it is not appended to the bottom of E, it will override the dates starting at cell E5

multiple dates can be added in 1 update


so for example if E5:E8 had the following dates

<table border="0" cellpadding="0" cellspacing="0" 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>


after the update takes place col E starting at E5 might look like this


<table border="0" cellpadding="0" cellspacing="0" width="76"><tbody><tr style="height: 15pt;" height="20"><td class="xl64" style="height: 15pt; width: 57pt;" width="76" height="20">9/22/2011</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">4/15/2012</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">7/16/2012</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">5/8/2013</td></tr></tbody></table><table border="0" cellpadding="0" cellspacing="0" width="76"><tbody><tr style="height: 15pt;" height="20"><td class="xl64" style="height: 15pt; width: 57pt;" width="76" height="20">12/20/2013</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">5/1/2014</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">6/31/2014</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">


thanks
</td></tr></tbody></table>

 

[Warship]

This will find your dates.

Sub DateFinder()
    Dim r As Long, z As Long
    Dim dataTable As Range
    Dim lookTable As Range
    
    'Data in Col J has to be sorted Past to Future, Top to Bottom for proper results
    
    Set dataTable = Sheets("Sheet1").Range("J2:J" & Sheets("Sheet1").Cells(Rows.Count, 10).End(xlUp).Row)
    Set lookTable = Sheets("Sheet2").Range("E5:E" & Sheets("Sheet2").Cells(Rows.Count, 5).End(xlUp).Row)
    
    For z = 1 To lookTable.Rows.Count
        'Find On/Before
        For r = 1 To dataTable.Rows.Count
            If dataTable.Cells(r, 1) = lookTable.Cells(z, 1) Then
                lookTable.Cells(z, 1).Offset(, 4) = dataTable.Cells(r, 1)
                Exit For
            ElseIf dataTable.Cells(r, 1) > lookTable.Cells(z, 1) Then
                lookTable.Cells(z, 1).Offset(, 4) = dataTable.Cells(r - 1, 1)
                Exit For
            End If
        Next r
        'Find On/After
        For r = dataTable.Rows.Count To 1 Step -1
            If dataTable.Cells(r, 1) = lookTable.Cells(z, 1) Then
                lookTable.Cells(z, 1).Offset(, 5) = dataTable.Cells(r, 1)
                Exit For
            ElseIf dataTable.Cells(r, 1) < lookTable.Cells(z, 1) Then
                lookTable.Cells(z, 1).Offset(, 5) = dataTable.Cells(r + 1, 1)
                Exit For
            End If
        Next r
    Next z
End Sub

 

[xelhelp]

i put the code you provided in the worksheet change handler.... column I in sheet2 just says "End Date" and there is nothing populated in column J in sheet2


here's the code i have in there...
thanks

Private Sub Worksheet_Change(ByVal Target As Range)


    Dim r As Long, z As Long
    Dim dataTable As Range
    Dim lookTable As Range
    
    Application.EnableEvents = False
    
    'Data in Col J has to be sorted Past to Future, Top to Bottom for proper results
    
    Set dataTable = Sheets("Sheet1").Range("J2:J" & Sheets("Sheet1").Cells(Rows.Count, 10).End(xlUp).Row)
    Set lookTable = Sheets("Sheet2").Range("E5:E" & Sheets("Sheet2").Cells(Rows.Count, 5).End(xlUp).Row)
    
    For z = 1 To lookTable.Rows.Count
        'Find On/Before
        For r = 1 To dataTable.Rows.Count
            If dataTable.Cells(r, 1) = lookTable.Cells(z, 1) Then
                lookTable.Cells(z, 1).Offset(, 4) = dataTable.Cells(r, 1)
                Exit For
            ElseIf dataTable.Cells(r, 1) > lookTable.Cells(z, 1) Then
                lookTable.Cells(z, 1).Offset(, 4) = dataTable.Cells(r - 1, 1)
                Exit For
            End If
        Next r
        'Find On/After
        For r = dataTable.Rows.Count To 1 Step -1
            If dataTable.Cells(r, 1) = lookTable.Cells(z, 1) Then
                lookTable.Cells(z, 1).Offset(, 5) = dataTable.Cells(r, 1)
                Exit For
            ElseIf dataTable.Cells(r, 1) < lookTable.Cells(z, 1) Then
                lookTable.Cells(z, 1).Offset(, 5) = dataTable.Cells(r + 1, 1)
                Exit For
            End If
        Next r
    Next z
    
Application.EnableEvents = True


End Sub

 

[xelhelp]

don't know if it helps but in cell J1 of sheet1 i have "End Date"

thanks

 

[xelhelp]

it looks like for whatever reason its taking whatever is in cell J1 of sheet1 and populates column I of sheet2 with it. nothing in column J of sheet2 is populated
thanks

 

[Warship]

Remove the Worksheet_Change Sub & try the code manually.
Do you get desired results?