Searching for words in excel

Hi,

I am quite new to VBA. I have stolen a little piece of code from another website and the code works very well with a little alteration. The code searches for a value inside a given cell. If it finds a match, it will then copy and paste the whole row into another sheet. Here is the code

Sub SearchForString()

    Dim LSearchRow As Integer
    Dim LCopyToRow As Integer

    On Error GoTo Err_Execute

    'Start search in row 1
    LSearchRow = 1

    'Start copying data to row 3 in Sheet2 (row counter variable)
    LCopyToRow = 2

    While Len(Range("A" & CStr(LSearchRow)).Value) > 0

        'If value in column B = "Voids", copy entire row to Voids
        If Range("B" & CStr(LSearchRow)).Value = "Voids" Then

            'Select row in All alarms to copy
            Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
            Selection.Copy

            'Paste row into Sheet2 in next row
            Sheets("Voids").Select
            Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
            ActiveSheet.Paste

            'Move counter to next row
            LCopyToRow = LCopyToRow + 1

            'Go back to Sheet1 to continue searching
            Sheets("All Alarms").Select

        End If

        LSearchRow = LSearchRow + 1

    Wend

    'Position on cell A3
    Application.CutCopyMode = False
    Range("A1").Select

    MsgBox "All matching data has been copied."

    Exit Sub
    
Err_Execute:
    MsgBox "An error occurred you tit, sort it out."

End Sub

What I wanted to do is alter the code marked in red to, rather then = the value "voids". I wanted to search to see if the cell contained the same word. If it does then do exactly the same as I am doing at the moment.

Thank you in advance for your help

Ashley

You need to wrap your code in code tags before the forum police get here!
Edit your post, click [Go Advanced], then highlight your code, and click the [Code] button. It makes it easier for people to read it and help you, and preserves your formatting

To be honest that code, although it works, does seem a bit long winded! Here is something that will do what you want.

Sub Using_Find()
Dim found As Range
Dim dest_row As Long
Dim first_addr As String

dest_row = 1

With Sheets("Data")
    On Error Resume Next
    Set found = .Range("B:B").Find(what:="Voids", LookIn:=xlValues, lookat:=xlPart)
    On Error GoTo 0
    If Not found Is Nothing Then
        first_addr = found.Address
        Do
            .Rows(found.Row).EntireRow.Copy Destination:=Sheets("Voids").Rows(dest_row).EntireRow
            dest_row = dest_row + 1
            Set found = .Range("B:B").FindNext(found)
        Loop While Not found Is Nothing And found.Address <> first_addr
    End If
End With
End Sub

NOTE: When working with multiple sheets it is always worth explicitly stating which sheet you want to reference. In your previous code you do with the "Voids" sheet, but not with the Sheet that contains your data. In my code above I have 'assumed' a name of "Data" for the sheet that contains all the data you are working from, so you will need to modify that to be correct.

This line is the one that starts the 'Find':

    Set found = .Range("B:B").Find(what:="Voids", LookIn:=xlValues, lookat:=xlPart)

It is the use of 'xlPart' in this line that will look for text within a cell without the entire cell having to match the word "Voids". If you wanted to only match complete cells you would use xlWhole instead.
Be aware though, that because you are now looking for it just containing that word, or rather those letters, if column B contained the word "Avoids" then that too would match.