VBA Problem: Autofilter criteria confuses text "Jan-13" (01/01/2013) with 13/01/2013

[Phil Morris]

Hello all,

I am suffering from a highly specific problem that I havent been able to find a viable answer for through the usual search engines. I am hoping someone here can help.

I am trying to autofilter a data set based on criteria that I parse from a Countifs formula. So the countif formula is as follows:

=Countifs(A:A,"Monday",B:B,"AM",.....)

This would then come up with an answer of say 6 matching rows. The macro is then supposed to filter column A for "Monday", column B for "AM" and so on, and hopefully return the 6 rows that the countifs counted.

I have managed all this except for in a specific scenario:

The countif formula is as follows:

=Countifs("A:A", "<>" & Curmon)

Where "Curmon" is a named range which holds the value "Jan-13" (numeric value for 01/01/2013 formatted as MMM-YY)

However in the middle of the code, I have to separate out the "<>" from the "Curmon" and evaluate each separately, and then combine them to be "<>Jan-13".

When I have done this, the filter treats the answer as "<> 13/01/2013" and as I dont have any results matching that criteria, returns all of my data.

I appologise for the awful look of my code, it is a work in progress, and I am sure pretty much every stage can be made more efficient but I wanted it working first. Here are the key parts:

For i = 1 To Range_Count
  Text = Range_Criteria(i, 2)
  If InStr(Text, "&") > 0 Then
    Pre_Text = Trim(Left(Text, InStr(Text, "&") - 1))
    Post_Text = Trim(Right(Text, Len(Text) - InStr(Text, "&")))
    If IsRangeName(Post_Text) Then
      Set Nm_Rng = ThisWorkbook.Names(Post_Text).RefersToRange
      Post_Text = Format(Nm_Rng.Value, Nm_Rng.NumberFormat)
    Else
      On Error GoTo Criteria_Not_Range1
      Set Nm_Rng = Range(Post_Text)
      If IsDate(Nm_Rng.Value) Then
        Post_Text = Format(Nm_Rng.Value, Nm_Rng.NumberFormat)
      Else
        Post_Text = Nm_Rng.Value
      End If
    End If
Continue_To_Next_Criteria1:
    Range_Criteria(i, 2) = Pre_Text & Post_Text
  End If
Next i

Range_Criteria(i,2) holds the Criteria element of each pairing within the Countifs formula.
IsRangeName is a function which checks if a string is a named range
All of the variables storing the data are string format, so pre_text, Post_Text and the array Range_Criteria are all strings.


Then at the end of the macro I loop using the following to filter the report, using in the problem example the string "<>Jan-13" for Criteria1.

ThisWorkbook.Names("Table_Start").RefersToRange.AutoFilter Field:=Field_Count, Criteria1:=Range_Criteria(i, 2), Operator:=xlFilterValues

Interestingly, the code works fine, if in the countif I use the string "<>Jan-13" directly. It is only when I calculate the string via VBA that the problem occurs. "<>" & "Jan-13" also does not work for the same reason.

I hope that makes sense to someone, appologies for the long post. Any questions, or more info on the code required, please fire away.

 

[Andrew Poulsom]

Does the column that you are filtering contain text or serial dates formatted as mmm-yy?

 

[Phil Morris]

The column contains serial dates formatted as MMM-YY

 

[Andrew Poulsom]

So how do you filter for not equal to Jan-13 manually? I can't do it in Excel 2010.

 

[Phil Morris]

I do appologise, you're right. "<>Jan-13" also doesnt work. Filtering by "Jan-13" does give the right results, but adding in any qualifier like "<" or ">" makes it revert to checking against "13/01/2013". I must have got myself confused with all the different options I have been trying.

So yes, now the problem is that invariably when using a qualifier like "<" ">" and then a date, whether it is referenced by "<>Jan-13", '"<>" & Curmon' or '"<>" & $A$1', all result in converting the date from Jan-13 to 13/01/2013 when using the macro

Manually when just working directly with the autofilter: "Does not Equal" and then "Jan-13" works fine. - Edit: I have just noticed, when going back into the auto filter, it has converted it to 01/01/2013, however this is fine, as all my dates will start with 1st of the month. If there is a work around I could use where I picked up the date, and then forced it to be the first, that might work.


Edit2: Excel is very odd....Just tried a few things manually with the auto filter
Filtering "Equals" "Jan-13" - returns the right results
Filtering "Does not Equal" "Jan-13" returns the right results - but it converts it to "Does not equal" "01/01/2013" and correctly blocks the Jan-13 entries
However then trying to filter: "Equals" "01/01/2013" returns no results, as it is looking for the formatted text and cant find any matches.

Looks like I would need to treat criteria where it equals a date different to criteria where it doesnt equal a date.

 

[Phil Morris]

Having been able to talk over the details above, I believe I have found a solution. Where the criteria is '"<>" & Curmon' or some variation thereof, I have changed the macro code to say:

If IsDate(Nm_Rng.Value) Then
    Post_Text = Format(Nm_Rng.Value, "MM/DD/YYYY")
Else
    Post_Text = Nm_Rng.Value
End If

This uses the dates in a format that the VBA recognises, and it sends the right results to the filter.

Hurrah.