Excel VBA Autofilter parameters problem 1

[Fenrirshowl]

Hi guys

I have data on a sheet called INPUT, which is effectively a record of post received - the date received is in column B / second column.

I want to take a copy of filtered information and put it on another sheet for amendment. After amendment changes are posted back to the INPUT sheet.

One of the filters that can be applied relates to date ranges on the "Date received" field.

The parameters are entered via a form. The FROM date is read from a textbox called tbDTRec and TO date is from a text box called tbDtRec2. The FROM date is always non-empty.

cbFrom is a combobox which has the entries "On" and "From" to define whether there is a "To" date.

Everything works fine, except when I am trying to filter between two dates when it returns no records, and I have no clue why. (I can exclude all other parameters of the filter and still get no records when filtering between two dates - I therefore think it has to be something about the piece of code below).

Can anyone explain what I have done wrong and/or provide a fix please?

Thanks in advance

Fen

dt1 = DateSerial(Year(tbDtRec.Text), Month(tbDtRec.Text), Day(tbDtRec.Text)) 'just to ensure we are dealing with a date!

If tbDtRec2.Text <> "" Then dt2 = DateSerial(Year(tbDtRec2.Text), Month(tbDtRec2.Text), Day(tbDtRec2.Text)) Else dt2 = ""

If cbFrom.Text = "On" Then
    Selection.AutoFilter Field:=2, Criteria1:=dt1
ElseIf cbFrom.Text = "From" And tbDtRec2.Text = "" Then
    Selection.AutoFilter Field:=2, Criteria1:=">=" & dt1
Else
    Selection.AutoFilter Field:=2, Criteria1:=">=" & dt1, Operator:=xlAnd _
    , Criteria2:="<=" & dt2
End If

 

[xlbo]

You must format your control dates to be exactly the same as the date field you are trying to filter:

dt1 = format(dt1,sheets("Sheet1").range("B2").format)

dt2 = format(dt2,sheets("Sheet1").range("B2").format)

Where "Sheet1" is the name of the sheet you are trying to filter on (assuming dates in col B, starting in row 2)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Fenrirshowl]

Thanks Geoff, but it's still not liking it.

I had a problem with

sheets("INPUT").range("B6").format

due to error 438 - Object doesn't support this property or method, so I amended it to

sheets("INPUT").range("B6").numberformat

Both dt1 and dt2 seem to be configured correctly after this(checked both sheet and variable formats with a msgbox), but I still get no records from the autofilter (and there are lots!).

Any other thoughts?

Additional info:

The cells on the worksheet are formatted: dd/mm/yyyy;@

I therefore amended your code to
dt1=format(dt1,"dd/mm/yyyy;@") etc
and ran it again: no records

Tried
dt1=format(dt1,"dd/mm/yyyy") etc
as well, just to see if there was a bit of a quirk and again, no records.

Also, can you enlighten me on why the autofilter worked (prior to writing this post) with just the from date, but not with both from and to?

Thanks

Fen

 

[xlbo]

sorry on the numberformat syntax - always forget that!

Only thing I can think of is that you have mixed formats in your date column (the @ makes some records text I presume where there is no date) which might be making excel throw a paddy

Can you format the whole date column as simply dd/mm/yyyy and force dt1 to the same format then try again?

Might also be worth trying:

Selection.AutoFilter Field:=2, Criteria1:=">=" & format(dt1,"dd/mm/yyyy")

in case VBA is forcing dt1 to a different number format when being concatenated with the ">="


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Fenrirshowl]

No worries on numberformat, I do it myself so it was a quick correction.

All of the formats are the same - I used a quick macro to check the format of each cell against the next with a msgbox alert if there was a difference and there were no problems, also included a debug.print and they were all the same:

Sub checkformats()
For x = 6 To 9999
    If Cells(x, 2).NumberFormat <> Cells(x + 1, 2).NumberFormat Then
        MsgBox x
    End If
    Debug.Print Cells(x, 2).NumberFormat
Next x
End Sub

I tried including the Format function in the autofilter line, and still no joy.

Additional information:

Entries of Input sheet are entered via a form. I had problems with obtaining the correct date so have used dateserial to ensure I obtained the right result. Formatting of the cells is not done via VB, but directly on the sheet - is that a problem?

The code for writing the entry is as follows:

Sheets("INPUT").Cells(x, 2) = DateSerial(Year(tbDtRecInput.Text), Month(tbDtRecInput.Text), Day(tbDtRecInput.Text))

I have noticed something odd about the format of the dates:
I select a cell and then bring up the format cells pop-up.
The selection in "category" is Date, the Type is *14/03/2001, Locale English (United Kindgom) [Yep - I'm UK based]
However, by habit, I always format my dates using Category=Custom, Type=dd/mm/yyyy

I have tried to change the formatting to dd/mm/yyyy - everything looks ok, I get no error message when I do it, yet when I then check the format afterwards it is still *14/03/2001. Could this be the issue? Any ideas how I fix it?

I did get worried about the asterix, so changed them to 14/03/2001 format. Then when I try to change it to dd/mm/yyyy it actually reverts to *14/03/2001. I don't understand that as I would think the two different formats *14/03/2001 and dd/mm/yyyy are two different formating types.

Just in case, I also entered the code:
Sheets("Input").Range("B6:B10034").NumberFormat = "dd/mm/yyyy;@"

and used the Format function in the autofilter line, to ensure the same format applies to the sheet and the parameter. No dice.

Given the above, any other suggestions?

 

[xlbo]

IS there any reason why you're using:

Sheets("Input").Range("B6:B10034").NumberFormat = "dd/mm/yyyy;@"

rather than :

Sheets("Input").Range("B6:B10034").NumberFormat = "dd/mm/yyyy"

That might be causing the * issue.....maybe it sees it as a variant of dd/mm/yyyy....



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xlbo]

Only other oprion I can think of right now is to format the whole lot to yyyymmdd which I believe is a "standard" format that just about anything (including VBA) recognises and doesn't try to change...

I have a feeling VBA is amending the format of the dates somehow - can you try putting in 13/12/2009 into your textbox date entry and test how it comes out in VBA - I am thinking you may get an error converting the date which would indicate that VBA is trying to process the date as mm/dd/yyyy...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Fenrirshowl]

Only that it is the format that seems to have been applied by default when data has been entered.

I have made both the following adjustments

Sheets("Input").Range("B6:B10034").NumberFormat = "dd/mm/yyyy"
&
Selection.AutoFilter Field:=2, Criteria1:=">=" & Format(dt1,"dd/mm/yyyy")

still nothing.

However, when checking the format with VB it is the "dd/mm/yyyy" which corresponds to the *14/03/2001, and "dd/mm/yyyy;@" which corresponds to 14/03/2001.

Many thanks for your time on this Geoff - it is appreciated.

 

[Fenrirshowl]

Geoff

Tried the 13/12/2008 and didn't get an error.

However.....
I have

....
Dim dt1 As Date
Dim dt2 As Date
..........
Sheets("Input").Range("B6:B10034").NumberFormat = "yyyymmdd"
.................
dt1 = DateSerial(Year(tbDtRec.Text), Month(tbDtRec.Text), Day(tbDtRec.Text))

dt1 = Format(dt1, "yyyymmdd")
.......

and it throws an error at dt1 = Format(dt1, "yyyymmdd") claiming there is a Type Mismatch !?!

I don't understand - dt1 is a date, surely a different data format shouldn't cause an error?

To get rid of the Type Mismatch, I removed the declarations (just to see..) but then got an overflow error at:

Selection.AutoFilter Field:=2, Criteria1:=">=" & Format(dt1, "yyyymmdd"), Operator:=xlAnd, Criteria2:="<=" & Format(dt2, "yyyymmdd").

 

[SkipVought]

dt1 = Format(dt1, "yyyymmdd")

is an oxymoron in terms of data type.

You're equating a DATE type with a STRING.

The Format function returns a string.

BTW, the Number Format, changes NOTHING. The underlying value is still a DateSerial Value. It makes no sense to change the format of the date variable. All that is is a DISPLAY convience for us humans.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Fenrirshowl]

Cheers Skip

But I should say "Nuts - I'm the moron" - I should have twigged the Format = String issue. May not be seeing the forest for the trees due to desperation! Not thinking clearly any more.

Wouldn't that mean that the Goeff's original thought about formatting of variable vs sheet wouldn't work, as the variable would be a string and the sheet values are dates?!
I think it would, but I've been staring at a screen for 10.5 hrs and my brain is fried. Time to go home and try again tomorrow!

 

[xlbo]

D'OH - should've asked what dt1 was dimmed as 1st off!!!

If dt1 was a string then the formatting to dd/mm/yyyy probably would've worked - that's why you have to have the exact same format as the worksheet when trying to equate a string with a date, it can do so as long as the same format is used

Because you are using proper dates, there should be no need to format at all although you may get saome interesting results when trying to append a date to a string in the autofilter code

Would try either with no formatting or change dt1 & dt2 to strings and format them as dd/mm/yyyy - one of those options should work!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Molby]

Does the following help?

http://www.ozgrid.com/VBA/autofilter-vba-dates.htm

 

[Fenrirshowl]

Ok, I've got it working.

I dimensioned dt1, dt2 as Long, used dt1=Dateserial(...) and then only referred to dt1 and dt2 in the filter criteria and that's that.
No Format() functions in VB, or reformatting of the spreadsheet is used.

Molby - thank you for the reference. I scanned it and thought it had covered what had previously been attempted, but after having cycled the variant types until I got the one I needed (just too lazy to look it up!) I eventually got to Long, and then saw it was in your referred link!

Geoff - many thanks for continued patience and effort. Well deserving of a star in appreciation.

Cheers all.

Fen