Search Range For Part Strings

rossfourfive · Aug 21st 2008

Hi,

I am using the following code to search a database of information and then display it on a different sheet.

The user types the search term into a textbox and then presses a command button to search the database.

Currently it only searches for an exact match. How can i adapt it so it searches for similar strings?

Thanks

Code

Sub SearchDatabase()


Dim rRange As Range
Dim rCell As Range
Dim ResultsOffset As Integer
Dim ResultsRange As Range


On Error Resume Next


    Set ResultsRange = Sheet2.Range("H13", Sheet2.Range("M65536").End(xlUp))
    ResultsRange.ClearContents
    
    Set rRange = Sheet1.Range("A1", Sheet1.Range("F65536").End(xlUp))
    ResultsOffset = 13
    
    For Each rCell In rRange


        If rCell = Sheet2.TextBox1 Then
        Sheet2.Range("H" & ResultsOffset) = Sheet1.Cells(rCell.Row, 1)
        Sheet2.Range("I" & ResultsOffset) = Sheet1.Cells(rCell.Row, 2)
        Sheet2.Range("J" & ResultsOffset) = Sheet1.Cells(rCell.Row, 3)
        Sheet2.Range("K" & ResultsOffset) = Sheet1.Cells(rCell.Row, 4)
        Sheet2.Range("L" & ResultsOffset) = Sheet1.Cells(rCell.Row, 5)
        Sheet2.Range("M" & ResultsOffset) = Sheet1.Cells(rCell.Row, 6)
        
        ResultsOffset = ResultsOffset + 1
        End If


    Next rCell
    
    ResultsOffset = ResultsOffset - 13
    
    If ResultsOffset = 0 Then
        MsgBox "No Match Found"
        Else
            If ResultsOffset = 1 Then
                MsgBox ResultsOffset & " entry found."
                Else
                MsgBox ResultsOffset & " entries found."
            End If
    End If
    


End Sub

Display More

**StephenR** · Aug 21st 2008

Re: Search Database For Similar Strings

How do you define "similar"? I would imagine using Find would be a better method.

rossfourfive · Aug 21st 2008

Re: Search Database For Similar Strings

Quote from StephenR

How do you define "similar"? I would imagine using Find would be a better method.

If i enter "ab" in the text box i want it to return all rows with "ab" in any of the cells. So it would return aberdeen, abstract, pablo etc. Any cells with ab anywhere in them would be returned.

How would i use the find method? Is there not a way i can get it to look for part of a string rather than an exact match with the code i already have.

Thanks for your help.

**StephenR** · Aug 21st 2008

Re: Search Database For Similar Strings

Hover over the word Find. There is a parameter to look in part of cells only.

Dave Hawley · Aug 22nd 2008

Re: Search Range For Part Strings

rossfourfive, I'll lower your assumed experience as using FIND is basic at best.

rossfourfive · Aug 22nd 2008

Re: Search Range For Part Strings

Quote from Dave Hawley

rossfourfive, I'll lower your assumed experience as using FIND is basic at best.

Mate, I appreciate you have to enforce your forum rules strictly but i really struggle to understand why this has to lead to the extremely patronising tone you use in almost every single post.

I can use find by following the guide you have written on it which is great but i don't want to use find for this. Is there no way i can adapt the code i am currently using to search for part strings? I know its possible to use *s to search for part strings but can it be used in conjunction with the textbox i am using?

Once again, thank you for your help.

Dave Hawley · Aug 22nd 2008

Re: Search Range For Part Strings

Because you had rated yourself above those answering YOUR question.

Quote

I can use find by following the guide you have written on it which is great but i don't want to use find for this.

Why?

rossfourfive · Aug 22nd 2008

Re: Search Range For Part Strings

Quote from Dave Hawley

Because you had rated yourself above those answering YOUR question.

Why?

I would consider my knowledge of excel to be above average. My knowledge of VBA however is basic. I apologise for the confusion.

Is there a way to adapt my code to search for a part string or is the only way to achieve this to re-write the code to use find instead?

If i am to use find how do i adapt -

Code

Cells.Find(What:="Cat", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).Activate

- to use the textbox as the search string and to print the results of the search on another sheet as i have done in my example in OP. Also which bit of the code makes it search for part strings?

Thanks

**pike** · Aug 22nd 2008

Re: Search Range For Part Strings

Hi rossfourfive

the find is a in-built function very fast al the guns first choice but

try Like by playing with

Code

If rCell = Sheet2.TextBox1 Then

to something similar to

Code

rCell like "*" & Sheet2.TextBox1 & "*"

untested but the module help is self explanatory

hope it helps
pike

**StephenR** · Aug 22nd 2008

Re: Search Range For Part Strings

Try this. It's untested so if it doesn't work, please post a sample workbook.

Code

Sub SearchDatabase()
     
    Dim rRange As Range
    Dim rCell As Range
    Dim ResultsOffset As Integer
    Dim ResultsRange As Range
    Dim i As Long
         
    On Error Resume Next
     
    Set ResultsRange = Sheet2.Range("H13", Sheet2.Range("M65536").End(xlUp))
    ResultsRange.ClearContents
    Set rRange = Sheet1.Range("A1", Sheet1.Range("F65536").End(xlUp))
    ResultsOffset = 13
     
    Set rCell = Sheet1.Range("A1")
    For i = 1 To WorksheetFunction.CountIf(rRange, Sheet2.Textbox1)
        Set rCell = rRange.Find(What:=Sheet2.Textbox1, After:=rCell, LookIn:=xlFormulas, LookAt:=xlPart, _
                                SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        If Not rCell Is Nothing Then
            Sheet2.Range("H" & ResultsOffset) = Sheet1.Cells(rCell.Row, 1)
            Sheet2.Range("I" & ResultsOffset) = Sheet1.Cells(rCell.Row, 2)
            Sheet2.Range("J" & ResultsOffset) = Sheet1.Cells(rCell.Row, 3)
            Sheet2.Range("K" & ResultsOffset) = Sheet1.Cells(rCell.Row, 4)
            Sheet2.Range("L" & ResultsOffset) = Sheet1.Cells(rCell.Row, 5)
            Sheet2.Range("M" & ResultsOffset) = Sheet1.Cells(rCell.Row, 6)
            ResultsOffset = ResultsOffset + 1
        Else
            MsgBox Sheet2.Textbox1 & " not found"
            Exit Sub
        End If
    Next i
     
End Sub

Display More

Cheeky Charlie · Aug 22nd 2008

Re: Search Range For Part Strings

I think instr might work - have a look at the help file, the syntax is pretty straightforward and you seem to know what you're doing. Get back to us if that doesn't help.

PS, sometimes I too find the great DH can OVERUSE the bold and italics to make you feel a bit WORTHLESS - remember he moderates about a thousand moron posts a day (I don't mean to include you with that) and has helped thousands of people via this forum

rossfourfive · Aug 22nd 2008

Re: Search Range For Part Strings

Pike,

I have played around with

Code

rCell Like "*" & Sheet2.TextBox1 & "*"

and used

Code

cellcheck = rCell Like "*" & Sheet5.TextBox1 & "*"
        If cellcheck = True Then

This does find part strings but i'm having a problem with it repeating rows and not clearing the results before each search. I will have another look at this if we can't get the find to work how i need it to. Thanks for the tip though.

Stephen,

I pasted your code into the workbook and couldn't get it to work. Played around with it a little but still no luck so i have attached a sample book as requested. Cheers for having a look at this.

Charlie,

Haven't had time to check this out but will look at it later, thanks for that.

**StephenR** · Aug 22nd 2008

Re: Search Range For Part Strings

I'm a bit mystified because it works for me. I changed it to Glasgow and Edinburgh and it worked. What happens when you try it?

**pike** · Aug 22nd 2008

Re: Search Range For Part Strings

rossfourfive mate
try...

Code

Sub SearchDatabaseTest()
     
    Dim rRange As Range
    Dim rCell As Range
    Dim ResultsOffset As Integer
    Dim ResultsRange As Range
     
    On Error Resume Next
     
    Set ResultsRange = Sheet2.Range("H13", Sheet2.Range("M65536").End(xlUp))
    ResultsRange.ClearContents
     
    Set rRange = Sheet1.Range("A1", Sheet1.Range("F65536").End(xlUp))
    ResultsOffset = 13
     
    For Each rCell In rRange
         
        If rCell Like "*" & Sheet2.TextBox1.Value & "*" Then
            Sheet2.Range("H" & ResultsOffset) = Sheet1.Cells(rCell.Row, 1)
            Sheet2.Range("I" & ResultsOffset) = Sheet1.Cells(rCell.Row, 2)
            Sheet2.Range("J" & ResultsOffset) = Sheet1.Cells(rCell.Row, 3)
            Sheet2.Range("K" & ResultsOffset) = Sheet1.Cells(rCell.Row, 4)
            Sheet2.Range("L" & ResultsOffset) = Sheet1.Cells(rCell.Row, 5)
            Sheet2.Range("M" & ResultsOffset) = Sheet1.Cells(rCell.Row, 6)
             
            ResultsOffset = ResultsOffset + 1
        End If
         
    Next rCell
     
    ResultsOffset = ResultsOffset - 13
     
    If ResultsOffset = 0 Then
         MsgBox "No Match Found"
    Else
        If ResultsOffset = 1 Then
            MsgBox ResultsOffset & " entry found."
        Else
            MsgBox ResultsOffset & " entries found."
        End If
    End If
     
     
End Sub

Display More

works for me

regards pike

:sheep:

rossfourfive · Aug 22nd 2008

Re: Search Range For Part Strings

Pike,

Thats working now, thanks a lot. One more thing...how would i change it so that the case doesn't matter in the search?

Thats one advantage of the find method Stephen is using...

Stephen,

If i type in "Glasgow" it finds them but not for part strings. So if i type "Glas" nothing happens. I'm keen to get this find method working properly as it seems to be the more efficient way of doing it.

**StephenR** · Aug 22nd 2008

Re: Search Range For Part Strings

OK, yes I see - have now amended CountIf line:

Code

Sub SearchDatabase()
     
    Dim rRange As Range
    Dim rCell As Range
    Dim ResultsOffset As Integer
    Dim ResultsRange As Range
    Dim i As Long
     
    On Error Resume Next
     
    Set ResultsRange = Sheet2.Range("H13", Sheet2.Range("M65536").End(xlUp))
    ResultsRange.ClearContents
    Set rRange = Sheet1.Range("A1", Sheet1.Range("F65536").End(xlUp))
    ResultsOffset = 13
     
    Set rCell = Sheet1.Range("A1")
    For i = 1 To WorksheetFunction.CountIf(rRange, "*" & Sheet2.TextBox1 & "*")
        Set rCell = rRange.Find(What:=Sheet2.TextBox1, After:=rCell, LookIn:=xlFormulas, _
                        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        If Not rCell Is Nothing Then
            Sheet2.Range("H" & ResultsOffset).Resize(, 6) = Sheet1.Cells(rCell.Row, 1).Resize(, 6).Value
            ResultsOffset = ResultsOffset + 1
        Else
             MsgBox Sheet2.TextBox1 & " not found"
            Exit Sub
        End If
    Next i
     
End Sub

Display More

rossfourfive · Aug 22nd 2008

Re: Search Range For Part Strings

Superb, that's it working.

Thanks everyone for your help.

**pike** · Aug 22nd 2008

Re: Search Range For Part Strings

rossfourfive

if you load up the sheet one Data list
you can see how quick find is compared to Like
find is a flash for 200 finds where 200 Likes takes a second

pike

Dave Hawley · Aug 23rd 2008

Re: Search Range For Part Strings

Quote

I would consider my knowledge of excel to be above average

Then how come you don't that FIND can search for part cell values and can be case sensitive or not?

I see you are NOW using the Find code, when you had convinced yourself it was no good

Search Range For Part Strings

Participate now!

Share

Users Viewing This Thread