Populate data in ListBox1 for valid year

[haseft]

Hi,
In Sheet1 there are some columns. 2 of the columns (Agreement_begin in column Z and Agreement_end in column AA) are formatted with date “yyyy-mm-dd”.
In UserForm1 there is a ComboBox2 with populated with year, for example 2016, 2017, 2018, a.s,o and a CommandButton1.
I want to populate data in ListBox1 (which has 5 columns), when I choose a year from ComboBox2 and press the CommandButton1.
The below codes doesn’t work.
Help with this please.

Agreement_begin (column Z)	Agreement_end (column AA)		Period (this table is only for visualizing)
2015-01-01	2016-12-31	->	2015	2016
2015-01-01	2018-12-31	->	2015	2016	2017	2018
2017-01-01	2019-12-31	->	2017	2018	2019
2017-01-01	2020-12-31	->	2017	2018	2019	2020
2018-01-01	2020-12-31	->	2018	2019	2020
2018-07-01	2019-06-30	->	2018	2019
2018-07-01	2022-06-30	->	2018	2019	2020	2021	2022
2019-01-01	2019-12-31	->	2019
2019-01-01	2022-12-31	->	2019	2020	2021	2022
2020-01-01	2021-12-31	->	2020	2021

The result av valid agreement in ListBox1 (for year choosen from ComboBox2) should be as below.
For example if I choose year 2018 then ListBox1 must show only 6 row of data (se bold rows above).

2015	2 row of data
2016	2 row of data
2017	3 row of data
2018	6 row of data
2019	7 row of data
2020	5 row of data
2021	3 row of data
2022	2 row of data

Private Sub CommandButton1_Click()
   Dim i As Long
   Set sht = ThisWorkbook.Worksheets("Sheet1")
   LastRow = sht.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
     
   Me.ListBox1.Clear
   TextBox4.Value = ""
   ListBox1.Value = ""
 
   With ListBox1
     .ColumnCount = 5
     .ColumnWidths = "190;70;70;50;50"
        For i = 2 To LastRow
           If sht.Range("Z" & i) >= CDate(ComboBox2.Value) And sht.Range("AA" & i) <= CDate(ComboBox2.Value) Then
             .AddItem Range("B" & i).Value
             .List(.ListCount - 1, 1) = sht.Range("Z" & i).Value
             .List(.ListCount - 1, 2) = sht.Range("AA" & i).Value
             .List(.ListCount - 1, 3) = sht.Range("U" & i).Value
             .List(.ListCount - 1, 4) = "$B$" & i
           End If
        Next i
   End With
   
End Sub

 

[NoSparks]

In the If line shouldn't you be comparing the combobox to the cells instead of the cells to the combobox ?

 

[DanteAmor]

If you only have the year in combobox2, then you must compare the year of the date.
Also the signs >= <= were inverted.
Try this:

Private Sub CommandButton1_Click()
   Dim i As Long, sht As Worksheet, LastRow As Long
   
   Set sht = ThisWorkbook.Worksheets("Sheet1")
   LastRow = sht.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
     
   Me.ListBox1.Clear
   TextBox4.Value = ""
   ListBox1.Value = ""
 
   With ListBox1
     .ColumnCount = 5
     .ColumnWidths = "190;70;70;50;50"
        For i = 2 To LastRow
           If Year(sht.Range("Z" & i)) <= Val(ComboBox2.Value) And _
              Year(sht.Range("AA" & i)) >= Val(ComboBox2.Value) Then
             .AddItem Range("B" & i).Value
             .List(.ListCount - 1, 1) = sht.Range("Z" & i).Value
             .List(.ListCount - 1, 2) = sht.Range("AA" & i).Value
             .List(.ListCount - 1, 3) = sht.Range("U" & i).Value
             .List(.ListCount - 1, 4) = "$B$" & i
           End If
        Next i
   End With
End Sub

 

[haseft]

I tried that too, no result.
Line if is wrong.

 

[DanteAmor]

I tried that too, no result.
Line if is wrong.

Please comment:
Did you put the complete code?
Did you modify something in the code that I sent?
Does it send you an error message?
In columns Z and A do you have dates or are they texts?

 

[dmt32]

I tried that too, no result.
Line if is wrong.

hi,
try it like this

 If Year(sht.Range("Z" & i).Value) >= Val(ComboBox2.Value) Then

Dave

 

[haseft]

thanks DanteAmor,
its working, I am still checkning the result.
one moe issue:
the value in column 4 in ListBox1 is nummer . I want to get 1000-separator, for example nummer 530000 shuould show 530 000, how?

.List(.ListCount - 1, 3) = sht.Range("U" & i).Value

 

[DanteAmor]

1000-separator, for example nummer 530000 shuould show 530 000, how

Try this:

.List(.ListCount - 1, 3) = Format(sht.Range("U" & i).Value, "000 000")

 

[haseft]

Thanks a lot, DanteAmor,

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.