CODEWITHSUNDEEP
excelvba
KROLN
KROLN

Reputation: 300

Populate Listbox with "cell.value < Date(now())"

it should be simple to make, but it doesn't work. My Column 6 from data list with dates is in that format "mmm.yyyy".

I become always all rows in my list, but i want only those with date older than today.

Only that part of statement work And ws.Cells(i, 6).Value <> vbNullString 

Sub PopulateList2()
  Dim rngName As Range
  Dim ws As Worksheet
  Dim i As Integer
  Dim LastRow As Long

  Set ws = E1G



AbgeListField.Clear
AbgeListField.ColumnCount = 2

LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).row

For i = 1 To LastRow

  'If statement to get Cells(i, 6).value and check it with Date(now).

  If Format(Cells(i, 6), "mmm.yyyy").Value < Format(Now(), "mmm.yyyy") _
  And ws.Cells(i, 6).Value <> vbNullString Then
    AbgeListField.AddItem ws.Cells(i, 1).Value
    AbgeListField.List(i - 1, 1) = ws.Cells(i, 2).Value
    AbgeListField.List(i - 1, 2) = ws.Cells(i, 3).Value
  End If
 Next i
End Sub

Upvotes: 0

Views: 355

Answers (1)

VBasic2008
VBasic2008

Reputation: 54815

Format vs Value

Maybe I misunderstood but this code should work fine:

If ws.Cells(i, 6).Value < Now() _
    And ws.cells(i, 6).value <> vbNullString Then

It couldn't work with format, because format turns a value into a string. Then you would have to use the CDate conversion function to get it back to a date, but what's the use of that when you already have a date.

There are three ways of returning or setting a cell value e.g.

let's use 3rd October 2018 in cell 'A1'

Cells("A1").Value returns '3.10.2018' (Maybe different depending on the system settings)
Cells("A1").Value2 returns '43376'
Cells("A1").Text returns in your example 'Oct 2018'

If you use 'Value' it will always, regardless of your formatting, use the 'Value', not the 'Text'.

If you got lost on the way you can always convert Now() and ws.Cells(i, 6).Value to a double type to make sure numbers are being compared:

If CDbl(ws.Cells(i, 6).Value) < CDbl(Now()) _
    And ws.cells(i, 6).Value <> vbNullString Then

Upvotes: 1

Related Questions