I watched your video
Searchable Lists in Excel - Easy Method https://www.youtube.com/watch?v=56GPcLAj55A and it worked however the search field needs to be exact in order for it work, I tried applying search wildcards like "*" but that did not work either, how do I create a search criteria that does not require the user to input the exact wording?
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Searchable Tables
0
Answers
0
Selected Answer
Hi Sam and welcome to the Forum.
You can change the macro (with Don's video tutorial) so it always searches for a part string (probably most applicable on the Name search). Just make the changes in bold below within the macro:
ElseIf FieldName = "Name" Then
'Filter by Name
MyTable.Range.AutoFilter Field:=2, Criteria1:="*" & UserInput & "*"Discussion
thank you very much John it worked however it is not picking up numbers or dates only text, how can I fix this?
SamJHB (rep: 2) Jun 18, '21 at 4:23 pm
Sam
Thanks for selecting my Answer.
Not sure what you mean above but VBA can be used to update a table (within the macro) instead of manually.
You'll need to modify the italics bits in the statement:
Thanks for selecting my Answer.
Not sure what you mean above but VBA can be used to update a table (within the macro) instead of manually.
You'll need to modify the italics bits in the statement:
Worksheets("SheetName").ListObjects("TableName").Refresh
John_Ru (rep: 6142) Jun 18, '21 at 4:41 pm
Sam
I'm confused- above I was answering on my phone and was pretty sure your point above had referred to needing to manually update a table.
The answer I offered was suitable for strings only (as I said, for Names in the video macro). Dates are stored in Excel as numbers (e.g. put =TODAY() in a cell then convert the cell format to General and you'll see currently the number is 44365) so searching is harder- if the user types "07" do you want 07 in the day, the month or the year (or all three)? Either way. it's not a simple search on the Excel number.
If you need to search partial dates or numbers, please create a new question detaiing your needs (and attach an example Excel file with whatever you've done beyond Don's code to realise your goal). Hopefully one of us will reply (but it's getting late here so it won't be me today!)
I'm confused- above I was answering on my phone and was pretty sure your point above had referred to needing to manually update a table.
The answer I offered was suitable for strings only (as I said, for Names in the video macro). Dates are stored in Excel as numbers (e.g. put =TODAY() in a cell then convert the cell format to General and you'll see currently the number is 44365) so searching is harder- if the user types "07" do you want 07 in the day, the month or the year (or all three)? Either way. it's not a simple search on the Excel number.
If you need to search partial dates or numbers, please create a new question detaiing your needs (and attach an example Excel file with whatever you've done beyond Don's code to realise your goal). Hopefully one of us will reply (but it's getting late here so it won't be me today!)
John_Ru (rep: 6142) Jun 18, '21 at 5:40 pm
P.s. you can search non-date numbers if you convert your table column to text and the format of the search cell C5.
John_Ru (rep: 6142) Jun 18, '21 at 5:48 pm