Search in a userform not exact wording

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, 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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

JamesW

Well-known Member
Joined
Oct 30, 2009
Messages
1,197
Hi there,

Give this a go:

Code:
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

Board Regular
Joined
Mar 17, 2009
Messages
67
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...?

Code:
    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

Well-known Member
Joined
Oct 30, 2009
Messages
1,197
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

Board Regular
Joined
Mar 17, 2009
Messages
67
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.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,421
Messages
5,596,033
Members
414,039
Latest member
southike

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
Top