Search in a userform not exact wording

[maramiro]

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, example
if he types "shirt", the the search will look for all document name that has shirt or shirts in their title.

Private Sub ok_1_Click()
'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").Select
Range("A2:AP5").Select
Selection.Delete Shift:=xlUp
Range("A6:AP6").Select
Selection.Delete Shift:=xlUp

'Search from the tab
Sheets("Result").Select
Cells(2, 53).Select
ActiveCell = Trim(Me.Document_box.Value) 'BA5 : copier first name
Unload Me
'AZ5 = match (row value)
'Copy Results in another sheet
counter = Cells(2, 52).Value
counter_1 = Cells(2, 52).Value + 1
Cells(counter, 1).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
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

 

[JamesW]

Hi there,

Give this a go:

Private Sub ok_1_Click()
    'if no name selected
    If Trim(Me.Document_box.Value) = "" Then
        MsgBox "Please write a document name."
        Exit Sub
    End If
    'Erase data
    Sheets("Result").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Delete shift:=xlUp
    Sheets("Search").Range("A6:AP6").Delete shift:=xlUp
    
    'Search from the tab
    'Sheets("Result").Cells(2, 53).Value = Trim(Me.Document_box.Value)
    
    For Each sht In ThisWorkbook.Sheets
        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
    Next sht
    Unload Me
End Sub

I can't quite understand why you have the bottom section of your code.

Basically, on my one, you type in your search (assuming on a userform) and click go. It searches for the uppercase version of your text in all the tabs (which it converts their names also to uppercase) and returns the tab name in the results tab.

You can adapt this to search documents etc if you wish.

Any questions, let me know.

 

[maramiro]

Thank you for your fast response. I m still new to all vba macros.

I have a question, "For each sht", whats sht? Dont we have to
Dim sht as string??
Set sht as .....???

Would this code also be helpfull for the person if he types "Spec" for instance, it will show him all document titles that has the word "spec", "specification", "document specifications" etc...?

    For Each sht In ThisWorkbook.Sheets
        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
    Next sht
    Unload Me
End Sub

 

[JamesW]

Hi maramiro,

Thank you for your fast response. I m still new to all vba macros.

No problem.

I have a question, "For each sht", whats sht? Dont we have to
Dim sht as string??
Set sht as .....???

sht is just my way of saying "Worksheet". I would recommend always defining your variables. In this case you would define sht as a Worksheet (Dim sht as Worksheet). You don't have to SET it to anything, becasue we are searching all sheets in your workbook.

Would this code also be helpfull for the person if he types "Spec" for instance, it will show him all document titles that has the word "spec", "specification", "document specifications" etc...?

Yes. Anything the user types into the box is taken care of in Me.Document_box.Value which is then compared against every sheet name in the workbook (using the FOR loop). It sets both values to Uppercase so that it can ignore any case issues.

 

[maramiro]

Hello James,
Unfortunatly, the code is still not working,
See code below. I might not be expressing myself good.
I m trying to copy all the titles found on the "Result" sheet to a new sheet called "search". so when the user types in the userform "specification" for instance, it will look all titles that has this word in the "Result" sheet and then it will copy/paste it in the Search Sheet.

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