conditionally add to a listbox

Question

I have literally struggled for about 8 hours today trying to get this to work and cant seem to work out where I am going wrong.

I have listbox1 and would like it to be populated with account numbers that are over 14 days old.

Column A hold the account numbers Column J holds the date

Here is the code I am using so far that was kindly provided to me, I have been amending it all day with no success

Private Sub UserForm_Initialize()
        Dim I As Integer
    Dim maxRow As Integer

    maxRow = 100
    ListBox1.Clear
    For I = 1 To maxRow
        If (DateDiff("d", Now, Range("J" & I).Value) > 14) Then
            ListBox1.AddItem Range("A" & I)
        End If
    Next I
    End Sub

I have also included a link to my document so you can see if its any other coding that could be causing it not work work.

Thank you in advance

Click here to download my document

Answer 1

Maybe this will work for you:

Private Sub UserForm_Initialize()

Dim I As Long
Dim maxRow As Long

ListBox1.Clear

With Worksheets("Sheet1")
    maxRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    For I = 1 To maxRow
        If IsDate(.Range("J" & I).Value) Then
            If Now() - .Range("J" & I).Value2 > 14 Then
                Reporting.ListBox1.AddItem .Range("A" & I).Value2
            End If
        End If
    Next I
End With

End Sub