Filter data with "contains" using cell search

Hello and best wishes to all,
Example attached with explanations
https://www.cjoint.com/c/HAbfUBvh3Gw
Best regards

Hello,
Thank you and happy new year to you too,
I successfully tried to copy the module codes into my Excel file. However, it is stated that it is necessary to create a macro, and since I have no idea about them, the filter does not work.
I will look into it further as best I can, and it would be honorable if you could grant me a little more help if possible.
Thank you for your assistance.
Best regards,
iKreal.

Second question completely different:

How could I add values to cells by entering them into just one?
Explanation:
I have my list:
lion
leopard
cheetah
panther
giraffe
tiger
snake

Now, in one cell, I want to add an item to the list (below it) by writing it in another. Let's say in C4:
elephant

The cell clears and the word is added below the list, and we get:
lion
leopard
cheetah
panther
giraffe
tiger
snake
elephant

I think this can be pretty simple to do, but I don't know much about it.
Thanks for future help!
Best regards, iKreal.

Good evening,
Post 5: when you say "otherwise it doesn't sort correctly," are we talking about sorting or filtering?
If it is indeed about filtering, please give me a few examples that don't work so I can fix them.

Regarding your question in 6: Here is a small improvement (let me know if this works for you or not), if the name entered in cell C1 cannot be found in the list, then it will be added to the end of the list; otherwise, the list will be filtered by that name. Therefore, cell C1 has a dual function: it either serves as a criterion for filtering or as an input box for entering a new item.
Replace the macro in module 1 with this one

Public Filtre As String Sub RechercheAvecFiltre() Application.ScreenUpdating = False Derlig = [A10000].End(xlUp).Row DerCol = [ZZ3].End(xlToLeft).Column If AutoFilterMode Then isOn = "On" Else isOn = "Off" Range(Cells(3, "A"), Cells(3, DerCol)).AutoFilter End If ActiveSheet.Range(Cells(3, "A"), Cells(Derlig, DerCol)).AutoFilter Field:=1, Criteria1:=Filtre NbVAl = Range("_FilterDataBase").Resize(, 1).SpecialCells(xlCellTypeVisible).Count - 1 If NbVAl = 0 Then ActiveSheet.AutoFilterMode = False Cells(Derlig + 1, "A").Value = [C1] End If End Sub

If this doesn't work for you, please specify what you would like.
Best regards

Good evening,
Thank you for your responses.
In fact, after modification, the filters are working correctly; it was that I had entered values in the cells that caused the problem, but that's resolved now!
However, I would like to add to the list at the bottom, yes, but not with the search, because the search does not include all the characters (for example: ??i?a? and not: caïman).
Thank you for your future assistance,
Sincerely,
iKreal.

Good evening,
Here is a button for adding a new item.
https://www.cjoint.com/c/HAcrW60YVew
Best regards

Good evening,
Thank you so much for your help.
However, would it be possible to test all the values in the list, to see if the element is not already in the list, and thus not add it twice?
Thank you again for the time you are giving me.
Best regards,
iKreal.

Hello,
Here is the small modification requested
https://www.cjoint.com/c/HAdb20h34kw
Best regards

Hello,
Thank you for your help, however the macro is not working correctly. I modified the cells according to mine, but it still says that the word is already registered, even though it is not. I don't know why, as I did everything as you said.
Here is the sheet:


And here is the code:

Sub Ajout()
 Application.ScreenUpdating = False
 DerLig = [A10000].End(xlUp).Row
 Set c = Range("A6:A" & [A10000].End(xlUp).Row).Find([A2], LookIn:=xlValues)
 If c Is Nothing Then
 Cells(DerLig + 1, "A") = [A2]
 Worksheets("Feuil1").Range("A2").ClearContents
 ActiveSheet.AutoFilterMode = False
 
 'Sort in alphabetical order
 [A5].Select
 ActiveWorkbook.Worksheets("Feuil1").Sort.SortFields.Clear
 ActiveWorkbook.Worksheets("Feuil1").Sort.SortFields.Add Key:=ActiveCell, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
 With ActiveWorkbook.Worksheets("Feuil1").Sort
 .SetRange ActiveCell.Range("A2:A" & [A10000].End(xlUp).Row)
 .Header = xlNo
 .MatchCase = False
 .Orientation = xlTopToBottom
 .SortMethod = xlPinYin
 .Apply
 End With
 Else
 Worksheets("Feuil1").Range("A2").ClearContents
 End If
End Sub

I just added the clear of the cell once finished.

EDIT :
I just noticed that you can add a new word, however you cannot add a word that is contained within another. For example, I cannot add the word "Biche" if the word "Bichette" is already there. And that's very problematic. :/

Thank you for your help.
Best regards,
iKreal.

Hello,
In the following line, nothing should be changed
.SetRange ActiveCell.Range("A2:A" & [A10000].End(xlUp).Row), you must leave A1
For the issue with almost identical names, replace this line

Set c = Range("A6:A" & [A10000].End(xlUp).Row).Find([A2], LookIn:=xlValues)

with

Set c = Range("A6:A" & [A10000].End(xlUp).Row).Find([A2], LookIn:=xlValues, lookat:=xlWhole)

it should work.
Best regards.

Hello,
Thank you for your response.
However, I really think it needed to change because when I added the word "biche" (to try), this is what happened:
I don't know if it's really due to that...

And at the bottom of the list, here's what I get: O_o

I'm sorry I couldn't add the image...
Here's what I get:

Without replacing 
Set c = Range("A6:A" & [A10000].End(xlUp).Row).Find([A2], LookIn:=xlValues)

with

Set c = Range("A6:A" & [A10000].End(xlUp).Row).Find([A2], LookIn:=xlValues, lookat:=xlWhole)

It works.

Thank you very much!
However, it is not possible to run the Add macro when I enter the word in the cell (without a button)?
Maybe by modifying the event?
Best regards,
iKreal.

Hello,
With the latest change (removal of the button)
https://www.cjoint.com/c/HAecNCuASYw
Regards

Hello,
Thank you, I used your modifications to create something personalized, now one last little thing:
When adding a word to the list, the selected cell becomes the "Animal" cell
I would therefore simply like the selected cell to be back to cell A2, if possible.

EDIT:
In trying again, the words are no longer being added, and I can't find the problem.
It seems to me that to select a cell you need to write:

Worksheets("Feuil1").Range("A2").Select

Even removing this line, it still doesn't work. I am sending you my file attached:
https://www.cjoint.com/c/HAekemH0w8q

In any case, thank you very much for all the time you took to help me, I really appreciate it.
Best regards,
iKreal.

Hello,
To select cell A2, you need to add the following line in the sheet module as the second to last line.

 Target.Select

which results in

Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Address = "$C$2" Then Filtre = Target RechercheAvecFiltre ElseIf Target.Address = "$A$2" Then Mot = Target Ajout End If Target.Select Application.EnableEvents = True End Sub 

the words are no longer being added, and I can't find the problem. However, the file you returned works well. Please give me examples that do not work.
https://www.cjoint.com/c/HAek5j4HnNk
Best regards.

My apologies, after reopening the file, everything is working properly. Thank you very much for your help!
Best regards,
iKreal.