Macro is not filtering properly

[TomeK712]

Hi All,

Today, without any main changes in my Workbook, I found out that one of my option doesn't work.
My function is filtering database basing on values from Data Validation, that's part of my code:

  ...  
If Worksheets("MENU").CheckBoxes("CheckBox1").Value = 1 Then
        Dim lngStart As Range, lngEnd As Range
            Set lngStart = Worksheets("MENU").Range("I17")
            Set lngEnd = Worksheets("MENU").Range("K17")
                If Worksheets("MENU").Range("H11").Value = "Inbound" Then
                    Worksheets("INBOUND").Range("$A$1:$Y$2000").AutoFilter Field:=11, _
                    Criteria1:=">=" & lngStart, _
                    Operator:=xlAnd, _
                    Criteria2:="<=" & lngEnd
...

After that, all rows are hidded. When I'll click on filtered Column 11 -> Data Filters -> Between... my values are there. When I click OK it works, filtering like supposed to.
Don't know what happened, but my Workbook, even after creating different macros with set-up values to filter is not accepting macros to filter properly.

Do you know what could be the source of the issue?

 

[MARK858]

Re: Macro is not filltering properly

What happens with

If Worksheets("MENU").CheckBoxes("CheckBox1").Value = 1 Then
        Dim lngStart As Range, lngEnd As Range
            Set lngStart = Worksheets("MENU").Range("I17")
            Set lngEnd = Worksheets("MENU").Range("K17")
                If Worksheets("MENU").Range("H11").Value = "Inbound" Then
                    Worksheets("INBOUND").Range("$A$1:$Y$2000").AutoFilter Field:=11, _
                    Criteria1:=">=" & [COLOR="#FF0000"]Clng[/COLOR](lngStart), _
                    Operator:=xlAnd, _
                    Criteria2:="<=" &[COLOR="#FF0000"] Clng[/COLOR](lngEnd)

 

[TomeK712]

Re: Macro is not filltering properly

Brilliant! Works perfectly!
Thanks!


Edit:
Nope, actually it doesn't work. After applying the Clng it filtering the date only, without the time.

the fortmat is: dd/mm/yyyy hh:mm

 

[TomeK712]

Re: Macro is not filltering properly

Is there option to use Clng with time, in format: dd/mm/yyyy hh:mm?
I'm looking online but nothing works.

 

[MARK858]

Re: Macro is not filltering properly

Try CDbl, a date and time is a whole number with a decimal where as a Long is a whole number only.

Not guaranteeing it will work though.

 

[TomeK712]

Re: Macro is not filltering properly

Thank you sooo much MARK8598 it works!

Now I know to spend some time to learn about type conversion functions, cheers!