Macro to search column for specific word within in a string then copy all examples to a new spreadsheet

[fredstonina]

I'm brand new to VB / Macros in excel but had a little experience with HTML - I thought I'd be able pick up how to write this code but I'm having a hard time. Here's the gist:

1. I get a new spreadsheet every month. It has a single column, is very long (+10,000) and has an unknown range - the range changes each month.

2. Each cell has a string of letters. I need to be able to find a specific word within that string. For example, if I'm looking for the word "dog" and A1 contains "athdogmns" then yes I want to know that. If A2 contains "athdjogal" then no I don't care about that.

3. I want to compile all of the "hits" into a new column, in a new spreadsheet. So if the macro found the word "dog" 6 times then I would have a new spreadsheet where it listed the full strings containing the word "dog."

Please let me know if there is something I'm not making clear - thank you!

 

[JLGWhiz]

Give this a try

Sub dog()
Dim c As Range, rng As Range, kennel As Variant
With Sheets(1)
Set rng = .Range("A1", .Cells(Rows.Count, 1).End(xlUp))
    For Each c In rng
        If InStr(c, "dog") > 0 Then
            kennel = Split(c.Value)
            For i = LBound(kennel) To UBound(kennel)
                If InStr(kennel(i), "dog") > 0 Then
                    Sheets(2).Cells(Rows.Count, 1).End(xlUp)(2) = kennel(i)
                End If
            Next
        End If
    Next
End With
End Sub

 

[JLGWhiz]

Use this one. The Split function is incorrect in the first one. I used a space as the delimiter because there was no other delimiter mentioned in the OP.

Sub dog()
Dim c As Range, rng As Range, kennel As Variant
With Sheets(1)
Set rng = .Range("A1", .Cells(Rows.Count, 1).End(xlUp))
    For Each c In rng
        If InStr(c, "dog") > 0 Then
            kennel = Split(c.Value, " ")
            For i = LBound(kennel) To UBound(kennel)
                If InStr(kennel(i), "dog") > 0 Then
                    Sheets(2).Cells(Rows.Count, 1).End(xlUp)(2) = kennel(i)
                End If
            Next
        End If
    Next
End With
End Sub

 

[fredstonina]

Wow - that is awesome, thank you so much - you've saved countless hours. Looking forward to understanding VB better in the future. Cheers.

 

[JLGWhiz]

Wow - that is awesome, thank you so much - you've saved countless hours. Looking forward to understanding VB better in the future. Cheers.

You're welcome,
regards, JLG

 

[cheekypoet]

Hi,

This macro is exactly what I need as well. I am a VB newbie and hoping to rely on the kindness of strangers.

I have managed to update it so it runs against my file values but I am not experienced enough to know how to return a range of cells rather that just the word found.

For example the output into Sheet 2 only returns the word I am searching for in a listing, what I would like for it to do is copy the entire row where the word was found and paste it into the second spreadsheet.


I am hoping JLGWhiz or someone else will be kind enough to update the code for me please. Happy to be pointed in the right direction so I can try and figure it out myself too.

I tried substituting this into the code with no success: (see total newbie with no idea)

If InStr(kennel(i), "dog") > 0 Then
cell.EntireRow.Copy Sheets(2).Cells(Rows.Count, 1).End(xlUp)(2) = kennel(i)
End If