Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Date and Listbox Date formats
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Date and Listbox Date formats
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" 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Date and Listbox Date formats
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" wrote in message ... 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" 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Date and Listbox Date formats
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" 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.... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Date and Listbox Date formats
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" wrote in message ... 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" 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.... |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Date and Listbox Date formats
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" wrote in message ... 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" wrote in message ... 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" 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.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
date in Cell to change colors if the date is beyond today's date | Excel Discussion (Misc queries) | |||
Dealing with date formats on expiration date | Excel Programming | |||
The Cell formats keep changing itself from text to date | Excel Discussion (Misc queries) | |||
date formats | Excel Discussion (Misc queries) | |||
Changing Cell formats to date fields automatically | Excel Worksheet Functions |