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

 

[xelhelp]

with the code now in a module and executed manually i get the same results as before.
column I in sheet2 just says "End Date" and there is nothing populated in column J in sheet2

 

[Warship]

Try putting DateFinder in seperate Module,
Then for Sheet2:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 5 Then DateFinder
    
End Sub

 

[Warship]

And you do have data in Sheet1 J2:J42 ?

 

[xelhelp]

that is correct i have data in Sheet1 J2:J42,

i tried putting DateFinder in seperate Module, i get the same results
column I in sheet2 just says "End Date" and there is nothing populated in column J in sheet2

would it help if i sent u the sheet? if so where?

 

[Warship]

Check your PM

 

[xelhelp]

any input from anyone else would be greatly appreciated!

 

[Warship]

Ye have little confidence I guess...

Got your workbook.

The problem was your lookup date (sheet2 col E) was formatted as date and your data (sheet1 Col J) was formatted general so the comparison wasn't apples to apples.

The DateValue deals with the format issue.

You snuck in the TW values on me. Added code for those.

Added code to handle blank look ups.

Also added code to ClearContents of columns I-L of last results

In a sep module put:

Option Explicit

Sub DateFinder()
    Dim r As Long, z As Long
    Dim dataTable As Range
    Dim lookTable As Range
    
    Sheets("Sheet2").Range("I5:L" & Sheets("Sheet2").Cells(Rows.Count, 9).End(xlUp).Row).ClearContents
    Application.ScreenUpdating = 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
        If lookTable.Cells(z, 1) <> "" Then
            'Find On/Before
            For r = 1 To dataTable.Rows.Count
                If DateValue(dataTable.Cells(r, 1)) = DateValue(lookTable.Cells(z, 1)) Then
                    lookTable.Cells(z, 1).Offset(, 4) = dataTable.Cells(r, 1)
                    lookTable.Cells(z, 1).Offset(, 6) = dataTable.Cells(r, 3)
                    Exit For
                ElseIf DateValue(dataTable.Cells(r, 1)) > DateValue(lookTable.Cells(z, 1)) Then
                    lookTable.Cells(z, 1).Offset(, 4) = dataTable.Cells(r - 1, 1)
                    lookTable.Cells(z, 1).Offset(, 6) = dataTable.Cells(r - 1, 3)
                    Exit For
                End If
            Next r
            'Find On/After
            For r = dataTable.Rows.Count To 1 Step -1
                If DateValue(dataTable.Cells(r, 1)) = DateValue(lookTable.Cells(z, 1)) Then
                    lookTable.Cells(z, 1).Offset(, 5) = dataTable.Cells(r, 1)
                    lookTable.Cells(z, 1).Offset(, 7) = dataTable.Cells(r, 3)
                    Exit For
                ElseIf DateValue(dataTable.Cells(r, 1)) < DateValue(lookTable.Cells(z, 1)) Then
                    lookTable.Cells(z, 1).Offset(, 5) = dataTable.Cells(r + 1, 1)
                    lookTable.Cells(z, 1).Offset(, 7) = dataTable.Cells(r + 1, 3)
                    Exit For
                End If
            Next r
        End If
    Next z
End Sub

And then worksheet2 should have:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 5 Then DateFinder
    
End Sub

 

[xelhelp]

ur Awesome! thank u sir!

i just started learning this stuff couple days ago so thanks for ur patience...

now i just have to learn the reasoning behind the code u wrote....

thanks again

 

[xelhelp]

one last thing...when the dates in column B updates, it updates the dates in column E, however this doesnt update the dates and values in column I, J, K, L i have to manually double click on a cell in column E for the change to take place...

 

[xelhelp]

i think i have a solution...
i took ur code and instead of putting it in a separate module...i just put at the bottom with the rest of the code in worksheet2, now when the dates in column B change it automatically updates all columns, E, I, J, K, L...
however is this going to cause problems in the future? not having ur code in a separate module?