-1

I stumbled over a strange problem today with the "contains" filter creteria used to filter a worksheet based on text input to a activeX text field in a worksheet.

All is shown in the attached screenshot.

The worksheet with the textbox "txtTitleSearch" that receives the input to be searched/filtered as "contains" criteria. Strangly Excel shows it after the code execution as filtered as "ending with" - which is not what was intended.

As prove I show also the worksheet with data over the searchterm.

What can possibly go wrong in this interpretation of vba code, that excel ends up filtering as "ending with"??

I expected filter results to match a "contains" filter with the search term entered.enter image description here

desertnaut
  • 57,590
  • 26
  • 140
  • 166
Spreader
  • 11
  • 3
  • Please, do not show code în pictures, since it can be place as editable, in case that somebody wants making some tests. Then, the used operator for a single criteria is not the most appropriate. Firstly, I would suggest you to use `Operator:=xlFilterValues`. And construction ending in `Text & "*"""` looks strange, except the case when you need that the filtered values to end in double quote (`"`). If not, it should be `Text & "*"`. In such a way, the rest of the string may be anything, as I suppose you need. – FaneDuru Jul 07 '23 at 11:16
  • When creating a 'complicated' ('double-quotes-infested') string, it is good practice to construct it in a separate Sub and test it with *Debug.Print*: `Debug.Print "=*" & "SomeString" & "*"""` will result in `=*SomeString*"` so you immediately see that something's wrong. – VBasic2008 Jul 07 '23 at 16:10

1 Answers1

4

When you set a filter using VBA, Excel attempts to interpret your request and selects the appropriate option for the front end.

In your code, your criteria is:

Criteria1:="*" & txtTitelSearch.txt & "*"""

This resolves to *text*" which, as it ends with a double quote, Excel sets the option to Ends With.

If you set your criteria to:

Criteria1:="*" & txtTitelSearch.txt & "*"

It resolves to *text* which, Excel will identify correctly as Contains because an asterisk is present at both the start and end of the input.

To summarise:

When looking for the text "SearchForThis", you would use the * wildcard in the following manner:

  • SearchForThis* Starts With that text
  • *SearchForThis* Contains that text
  • *SearchForThis Ends With that text
CLR
  • 11,284
  • 1
  • 11
  • 29