Macro for cells containing certain text

[benjg07]

Hi there

Need some help. Currently this macro only finds cells containing the exact text entered into the text box Search_OrganName.

I would like it to find any cell from the range that contains that text rather than just exact matches.

I'm thinking there is a really obvious solution I'm just not seeing.

Hope this question makes sense.

Thanks people

Sub searchontacts_specific()


'Marco to search for a specific contact by Organisation Name from the database


Dim Search_OrganName As String
Dim i As Integer


Sheet3.Range("SearchResults").ClearContents


Search_OrganName = Sheet3.Search_OrganName.Value


Sheet4.Activate


finalrow = Sheet4.Range("B100000").End(xlUp).Row


For i = 2 To finalrow


    If Cells(i, 2) = Search_OrganName Then


'Here is the problem. It only finds exact matches to the text entered into Search_OrganName text box
'I would like it to find all cells containing text entered
    
        Range(Cells(i, 1), Cells(i, 14)).Copy


        Sheet3.Activate


        Range("L300").End(xlUp).Offset(1, 0).PasteSpecial


        End If


Sheet4.Activate


Next i


Sheet3.Activate


MsgBox "Search Completed. Good Job!"


End Sub

 

[JLGWhiz]

Change this

If Cells(i, 2) = Search_OrganName Then

To this

If InStr(Cells(i, 2), " " & Search_OrganName & " ") > 0 Then

 

[benjg07]

Hi thanks

Really helped. Ended up learning a lot about the InStr function.

 If InStr(Cells(i, 2), Search_OrganName) > 0 Or Cells(i, 2) = Search_OrganName Then

ended up using this and it works really well.

Thanks

 

[JLGWhiz]

Hi thanks

Really helped. Ended up learning a lot about the InStr function.

 If InStr(Cells(i, 2), Search_OrganName) > 0 Or Cells(i, 2) = Search_OrganName Then

ended up using this and it works really well.

Thanks

I would think the Or part is unnecessary, but if it works for you then that is what counts.
Regards, JLG