Cell Date and Listbox Date formats

[Corey]

I am trying to get the correct line of code to ensure a MATCH is made in 2
dates.
1 date is in a sheet in the "ddd dd mmm yy" format, and the other is in the
dd/mm/yyyy format in a userfrom Listbox.

The line of code is highlighted by the arrow.
The dates DO MATCH, but i do not get a result as the dates are in different
formats, and retrun a FASLE Positive result.

I tried changing the .Value of each to VAL(.Cells) & Val(Listbox) etc but
NOTHING
Also tried changing the .Value to .Date also with NO result.

Can someone asist with this ?

Private Sub ListBox4_Click()
Application.ScreenUpdating = False
ListBox5.Clear
ListBox7.Clear
ListBox6.Clear
Dim LastCell As Long
Dim myrow As Long
LastCell = Worksheets("Data").Cells(Rows.Count, "D").End(xlUp).Row
With ActiveWorkbook.Worksheets("Data")
For myrow = 1 To LastCell
If .Cells(myrow, 4).Offset(, -3).Value <> "" And .Cells(myrow,
4).Offset(, -3).Value = ListBox1.Value And _
ListBox2.Value = .Cells(myrow, 4).Offset(, 3).Value And _
ListBox4.Value = .Cells(myrow, 4).Value Then '
<============== HERE
ListBox7.AddItem .Cells(myrow, 4).Offset(, 231).Value
ListBox5.AddItem .Cells(myrow, 4).Offset(, 230).Value
ListBox6.AddItem .Cells(myrow, 4).Offset(, 7).Value
End If
Next
End With
Sheets("Opening Page").Activate
Application.ScreenUpdating = True
End Sub

 

[Jim Cone]

Corey,
In your code, try formatting the cell date values the same as the listbox items...

ListBox2.Value = Format(.Cells(myrow, 4).Offset(, 3).Value , "dd/mm/yyyy")
--
Jim Cone
Portland, Oregon USA



"Corey"
<corey***@planetearth.com.au>
wrote in message
I am trying to get the correct line of code to ensure a MATCH is made in 2
dates.
1 date is in a sheet in the "ddd dd mmm yy" format, and the other is in the
dd/mm/yyyy format in a userfrom Listbox.
The line of code is highlighted by the arrow.
The dates DO MATCH, but i do not get a result as the dates are in different
formats, and retrun a FASLE Positive result.
I tried changing the .Value of each to VAL(.Cells) & Val(Listbox) etc but
NOTHING
Also tried changing the .Value to .Date also with NO result.
Can someone asist with this ?

Private Sub ListBox4_Click()
Application.ScreenUpdating = False
ListBox5.Clear
ListBox7.Clear
ListBox6.Clear
Dim LastCell As Long
Dim myrow As Long
LastCell = Worksheets("Data").Cells(Rows.Count, "D").End(xlUp).Row
With ActiveWorkbook.Worksheets("Data")
For myrow = 1 To LastCell
If .Cells(myrow, 4).Offset(, -3).Value <> "" And .Cells(myrow,
4).Offset(, -3).Value = ListBox1.Value And _
ListBox2.Value = .Cells(myrow, 4).Offset(, 3).Value And _
ListBox4.Value = .Cells(myrow, 4).Value Then ' <= HERE
ListBox7.AddItem .Cells(myrow, 4).Offset(, 231).Value
ListBox5.AddItem .Cells(myrow, 4).Offset(, 230).Value
ListBox6.AddItem .Cells(myrow, 4).Offset(, 7).Value
End If
Next
End With
Sheets("Opening Page").Activate
Application.ScreenUpdating = True
End Sub

 

[Corey]

Thanks for the reply Jim, but i am still get get a result.

If possible i really need to FORMAT the Listbox to be : ddd dd mmm yy

Is that possible ?

Corey....

 

[Jim Cone]

You have to format the dates before you add them to the ListBox.
Also, have you checked for extra spaces in the ListBox text?
--
Jim Cone
Portland, Oregon USA



"Corey"
<corey***@planetearth.com.au>
wrote in message
Thanks for the reply Jim, but i am still get get a result.
If possible i really need to FORMAT the Listbox to be : ddd dd mmm yy
Is that possible ?
Corey....

 

[Corey]

50% working.One date is formatted in the sheet as : 22/9/08, and is
displayed in the listbox as : 9/22/08, but allows the procedure to return a
CORRECT result as expected.

BUT,
another date is formatted in the sheets as : 11/12/08, and is displayed in
the listbox as : 12/11/08, but doe not allow the procedure to return as
correct result as it SHOULD.

Corey....

 

[Mike Fogleman]

Try using CDate on your cell values in the code.
CDate recognizes date formats according to the locale setting of your
system. The correct order of day, month, and year may not be determined if
it is provided in a format other than one of the recognized date settings.
In addition, a long date format is not recognized if it also contains the
day-of-the-week string.

If .Cells(myrow, 4).Offset(, -3).Value <> "" And CDate(.Cells(myrow,
4).Offset(, -3)).Value = ListBox1.Value And _
ListBox2.Value = CDate(.Cells(myrow, 4).Offset(, 3)).Value And _
ListBox4.Value = CDate(.Cells(myrow, 4)).Value Then '

untested, but some combination should get you what you want.

Mike F