Autofilter Issue

[skull_eagle]

Hi,

I'm trying to filter a column by date.

Less than cell value to greater than cell value. Cell value is in Date format.

Instead of using the date in the filter it is using the numeric value of the date eg. 32300

I don't understand why the below won't appear as 06/06/1988 instead of 32300
Criteria1:="<" & Format([DateVal], "dd/mm/yyyy")

Any help would be greatly appreciated.

Public WithEvents mLabelGroup2 As MSForms.ComboBox

Private Sub mLabelGroup2_Click()

''-- A live filter based on the value of the Combo boxes on the search form

Dim ControlName2, ColNum2
Dim Rowz As Long
Dim wsRD As Worksheet
Dim DateVal As Date

On Error Resume Next

    Set wsRD = Worksheets("RawData")
    Set wsRD = ActiveSheet


ControlName2 = mLabelGroup2.Name

ColNum2 = Replace(ControlName2, "CM_", "")


i = Search_Form.Controls("CM_" & ColNum2).Value


If Search_Form.Controls("CM_" & ColNum2).Value = "" Then
   
        
    ActiveSheet.ListObjects("Table_RawData").Range.AutoFilter Field:=ColNum2
    
Else
    
    ActiveSheet.ListObjects("Table_RawData").Range.AutoFilter Field:=ColNum2, _
    Criteria1:=i
                         
End If

           
DateVal = Worksheets("Admin").Range("P6")

If ColNum2 = 6 Then
    
If Search_Form.Controls("CM_" & ColNum2) = "< 35" Then
    
    ActiveSheet.ListObjects("Table_RawData").Range.AutoFilter Field:=6, _
    Criteria1:="<" & Format([DateVal], "dd/mm/yyyy")
    
    
ElseIf Search_Form.Controls("CM_" & ColNum2) = "> 35" Then

    ActiveSheet.ListObjects("Table_RawData").Range.AutoFilter Field:=6, _
    Criteria1:=">" & Format([DateVal], "dd/mm/yyyy")

Else

    ActiveSheet.ListObjects("Table_RawData").Range.AutoFilter Field:=6

End If
End If

 

[MARK858]

If you are wanting to to filter dates using greater than you want it to filter based the numeric value, not a text string which is what you are doing using Format

 

[skull_eagle]

If you are wanting to to filter dates using greater than you want it to filter based the numeric value, not a text string which is what you are doing using Format

But if all the Values in the column are in Date format the numeric value doesn't work.
I've just recently added the formatting code to try and get it working.

 

[MARK858]

A format is just an appearance of the cell, you want to use it's numeric value.
For example with the data set below (with the dates formatted as dd/mm/yyyy)

Book1.xlsb
	C	D	E
1			15/02/2023
2	test
3	10/02/2023
4	09/02/2023
5	14/02/2023
6	20/02/2023
7	19/02/2023
8	13/02/2023
9	26/02/2023
10	23/02/2023
11	17/02/2023
12	12/02/2023
13	18/02/2023
14	25/02/2023
15	22/02/2023
16	07/02/2023
17	24/02/2023
18	21/02/2023
19	05/02/2023
20	06/02/2023
21	16/02/2023
22	08/02/2023
23	11/02/2023
Sheet1

using the code

Sub Filterit()
  
   Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row).AutoFilter 1, ">" & CLng(Range("E1"))

End Sub

I get the result

Book1.xlsb
	C	D	E
1			15/02/2023
2	test
6	20/02/2023
7	19/02/2023
9	26/02/2023
10	23/02/2023
11	17/02/2023
13	18/02/2023
14	25/02/2023
15	22/02/2023
17	24/02/2023
18	21/02/2023
21	16/02/2023
Sheet1

If I use Format

Sub Filterit()
   
   Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row).AutoFilter 1, ">" & Format(CLng(Range("E1")), "dd/mm/yyyy")

End Sub

I get

Book1.xlsb
	A	B	C	D	E
1					15/02/2023
2			test
25
26
27
28
29
30
31
32
33
Sheet1

As the text is being seen as greater than any number

 

[skull_eagle]

A format is just an appearance of the cell, you want to use it's numeric value.
For example with the data set below (with the dates formatted as dd/mm/yyyy)

Book1.xlsb
	C	D	E
1			15/02/2023
2	test
3	10/02/2023
4	09/02/2023
5	14/02/2023
6	20/02/2023
7	19/02/2023
8	13/02/2023
9	26/02/2023
10	23/02/2023
11	17/02/2023
12	12/02/2023
13	18/02/2023
14	25/02/2023
15	22/02/2023
16	07/02/2023
17	24/02/2023
18	21/02/2023
19	05/02/2023
20	06/02/2023
21	16/02/2023
22	08/02/2023
23	11/02/2023
Sheet1

using the code

Sub Filterit()
 
   Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row).AutoFilter 1, ">" & CLng(Range("E1"))

End Sub

I get the result

Book1.xlsb
	C	D	E
1			15/02/2023
2	test
6	20/02/2023
7	19/02/2023
9	26/02/2023
10	23/02/2023
11	17/02/2023
13	18/02/2023
14	25/02/2023
15	22/02/2023
17	24/02/2023
18	21/02/2023
21	16/02/2023
Sheet1

If I use Format

Sub Filterit()
  
   Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row).AutoFilter 1, ">" & Format(CLng(Range("E1")), "dd/mm/yyyy")

End Sub

I get

Book1.xlsb
	A	B	C	D	E
1					15/02/2023
2			test
25
26
27
28
29
30
31
32
33
Sheet1

As the text is being seen as greater than any number

Okay, so if I double click in the date cells they will then get filtered correctly.
Any thoughts on how I can get them in the right format what I'm entering the value from the userform?

 

[MARK858]

if you put the formula

=isnumber(A2)

in a blank cell changing the A2 to the cell reference of one of the cells containing the date do you get FALSE?

 

[skull_eagle]

if you put the formula

=isnumber(A2)

in a blank cell changing the A2 to the cell reference of one of the cells containing the date do you get FALSE?

Yes, a FALSE result was returned.

 

[MARK858]

On a copy of your workbook (because we are altering data so you don't want to test on the original)

Select your dates
On the Data tab click Text To Columns
Delimited
Click Next
Make sure all the checkboxes are empty
Click Next
Click the Date check box with the option set as DMY
Click Finish

Format the cells as date again if needed

Does the formula return TRUE now for all the cells?

 

[skull_eagle]

On a copy of your workbook (because we are altering data so you don't want to test on the original)

Select your dates
On the Data tab click Text To Columns
Delimited
Click Next
Make sure all the checkboxes are empty
Click Next
Click the Date check box with the option set as DMY
Click Finish

Format the cells as date again if needed

Does the formula return TRUE now for all the cells?

That works perfectly. I ended up using the below code.

Thank you very much for your help.

    wsRD.Cells(j, 6).TextToColumns Destination:=Range("F" & j), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True

 

[MARK858]

You're welcome.

Just make sure that you use

wsRD.Range("F" & j)

rather than

Range("F" & j)