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 ....
The Silliest Question is generally
the one i forgot to ask.

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 ....
The Silliest Question is generally
the one i forgot to ask.

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" <(E-Mail Removed)> wrote in message
news:e%(E-Mail Removed)...
> 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 ....
> The Silliest Question is generally
> the one i forgot to ask.
>
>

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....

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....

"Jim Cone" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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....
>
>

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


"Corey" <corey***@planetearth.com.au> wrote in message
news:(E-Mail Removed)...
> 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....
>
> "Jim Cone" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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....
>>
>>
>
>