Search engine of non exact word using a userform

maramiro

Board Regular
Joined
Mar 17, 2009
Messages
67
I have a userform for the user to use a search engine on what he needs.
the following code I have written it, but it is only good if he writes exact wording of the document name.
How can I twist the code to make the search look for approximate words,

the search engine will try to look in for all the titles on "Result" worksheet and copy/past it to a new sheet called "search". so when the user types in the userform "specification" for instance, it will look all titles (Specifications, specification, spec, brown color spec) that has this word in the "Result" sheet and then it will copy/paste it in the Search Sheet.

Code:
Private Sub ok_1_Click()
Dim sht As Worksheet
Set sht = Worksheets("Result")
'if no name selected
If Trim(Me.Document_box.Value) = "" Then
MsgBox "Please write a document name."
Exit Sub
End If
'Erase data
Sheets("Search").Range("A6:AP6").Delete Shift:=xlUp
'Search from the tab
Sheets("Result").Cells(2, 53).Value = Trim(Me.Document_box.Value)
With sht
If UCase(sht.Name) Like "*" & UCase(Me.Document_box.Value) & "*" Then
Sheets("Result").Range("A" & (Range("A" & Rows.Count).End(xlUp).Row) + 1).Value = sht.Name
End If
Unload Me
End With
'Copy Result in another sheet
Sheets("Result").Select
counter = Cells(2, 52).Value
counter_1 = Cells(2, 52).Value + 1
Cells(counter, 1).Select
Range(Selection, Selection.End(xlToRight)).Select ' to make sure everything is selected
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
ActiveSheet.Range("F2").Select
Sheets("Search").Select
ActiveSheet.Range("A2").Select
ActiveSheet.Paste
ActiveSheet.Range("A2").Select
Sheets("Result").Select
If Cells(2, 54) = Cells(2, 55) Then
ActiveSheet.Cells(counter_1, 1).Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
ActiveSheet.Range("F2").Select
Sheets("Search").Select
ActiveSheet.Range("A2").Select
ActiveSheet.Paste
ActiveSheet.Range("A2").Select
Else
Sheets("Search").Select
End If

End Sub
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
so when the user types in the userform "specification" for instance, it will look all titles (Specifications, specification, spec, brown color spec) that has this word in the "Result" sheet and then it will copy/paste it in the Search Sheet.

Are you saying you want the code to figure out that when the user types "specification", it should find entries with the word "spec" in it? If so, what should happen if the word specific appears in the text in a cell? Or did I misunderstand your question.
 
Upvote 0
no, you have well understood it.
I want the user once he types any word in the same category, all results appears to him.
Like you do with google sometimes, you type one word in your search and all words of the same family appears to you.
I want the exact matching of the word and all of its family to appear once the user types the word.
 
Upvote 0
no, you have well understood it.
I want the user once he types any word in the same category, all results appears to him.
Like you do with google sometimes, you type one word in your search and all words of the same family appears to you.
I want the exact matching of the word and all of its family to appear once the user types the word.

I do not know how to do that, but I suspect you would need to create the list of "family words" for each word you want to search on so that the code could look it up.
 
Upvote 0
Thank you. I thought it is possible to do it since it will something similar to the custom filler of Excel.
How does the custom filter happens on excel? it will be something similar to it.
when you are looking for a word not exact, you put a " * " in front of the word, and all the family category will show up.
Am i wrong?
 
Upvote 0

Forum statistics

Threads
1,207,439
Messages
6,078,569
Members
446,349
Latest member
Malroos7912

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top