Excel VBA: For Each Cell In Column A3:A If Functions

Question

I have a ListBox that I am trying to populate based on For Each Cell In Column A3:A If CDate(Cell.Value) < 2 Years.

Essentially looking at Column A3 till last used cell for date in year less than 2 years to return value from adjacent Column B if true to the ListBox.

Thank You.

 Dim MyDateYear As Date
 Dim Cell As Range

 MyDateYear = Date
 MyDateYear = DateAdd("m", -24, MyDateYear)

 For Each Cell In Worksheets("Sheet1").UsedRange.Columns("A").Cells
 If CDate(Cell.Value) < Format(MyDateYear, "YYYY") Then
 Me.ListBox1.AddItem Cells(Cell.Row, 2).Value
 End If

 Next Cell

Answer 1

Try this instead

Dim MyDateYear As Date
Dim Cell As Range
Dim LRow as Long

MyDateYear = Date
MyDateYear = DateAdd("m", -24, MyDateYear)

With Workbooks(REF).Sheets("Sheet1") 'edit reference
    LRow = .Cells(.Rows.Count, "A").End(xlUp).Row

    For Each Cell In .Range("A3:A" & LRow)
        If CDate(Cell.Value) < Format(MyDateYear, "YYYY") Then
            Me.ListBox1.AddItem .Cells(Cell.Row, 2).Value
        End If
    Next Cell
End With

For more information on getting the last cell, row, column, read this article