User defined filter not working as expected - "contains" interpreted as "ending with"
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.
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*SearchForThisEnds With that text
- Recognize and process quotation mark (")
- Enumerate all data in Excel range, count them and sort by occurrence
- Add headers to table based on values found in cells
- Table Stacking using VBA
- how to handle excel files using C++?
- sqlite3 or CSV files
- Match and list values to a dynamic list including multiple column criteria
- Match and list values to a list including mutliple column criteria
- Transpose Excel data for multi-Stacked bar chart
- Match and list values based on values listed in another column
- Use pandas to read excel files with wrong sheet state
- Display x-rows below a certain position from a filtered and sorted list
- Get the first and last number occuring in a (text) string in a cell
- Count the program-steps
- Using VSTACK to pile-up results
- Excel vba Login to the site
- How to get a string array in an Excel cell and use as a reference
- Excel's max function returning wrong value when using vlookup
- Excel list of values where data matches across multiple rows
- How to save Image independently in VBA module
- How to map table references in a JSON file to corresponding values in an Excel file using Python?
- Move values from column to rows in Excel
- Application.FileDialog vs GetOpenFilename: What is the difference?
- Only display the values in a certain rank position from filtered and sorted list
- Convert table to reflect column names as row values
- Excel: Dynamic stacking or arrays n-number of times
- Convert Excel formula to Power Query (if countifs)
- Trying to hide rows in excel based on two values
- Combine every unique value from column A with duplicated values into a new sheet
- Import formula from variable - Excel VBA