Doing a search for text within a call, from entries in a defined name range

[Upex]

Hi All,

Hope you're well.

With support from BrianMH and Snakehips from a past problem (thanks again chaps), I created the below which checks the cell contents of sheet1 columns G and H for the words/phrases listed in sheet2 range b4 to bXXX and if it finds the word/phrase in either G/H then it adds a Yes in Sheet1 col A (for the same row of course).

Sub highlight_matches_found_in_list()

    Dim r As Range
    Dim c As Range
    Dim i As Integer
    Dim x As Integer
    Dim sColumn As String
    Dim vSplit As Variant
    Dim sTest As String
    For i = 1 To 2
        If i = 1 Then
            sColumn = "G"
        Else
            sColumn = "H"
        End If
        Set r = Range(Range(sColumn & 3), Range(sColumn & Sheet1.Rows.Count).End(xlUp))
        For Each c In r.Cells
            vSplit = Split(c, " ")

            For x = LBound(vSplit) To UBound(vSplit)
                sTest = Replace(vSplit(x), ",", "")

                If sTest Like Sheet2.Range("b4").Value Then
                    Range("A" & c.Row) = "Yes"
                    GoTo GotOne
                End If
                 If sTest Like Sheet2.Range("b5").Value Then
                    Range("A" & c.Row) = "Yes"
                    GoTo GotOne
                End If
                 If sTest Like Sheet2.Range("b6").Value Then
                    Range("A" & c.Row) = "Yes"
                    GoTo GotOne
                End If
                 If sTest Like Sheet2.Range("b7").Value Then
                    Range("A" & c.Row) = "Yes"
                    GoTo GotOne
                End If
                 If sTest Like Sheet2.Range("b8").Value Then
                    Range("A" & c.Row) = "Yes"
                    GoTo GotOne
                End If
                 If sTest Like Sheet2.Range("b9").Value Then
                    Range("A" & c.Row) = "Yes"
                    GoTo GotOne
                End If
                 If sTest Like Sheet2.Range("b10").Value Then
                    Range("A" & c.Row) = "Yes"
                    GoTo GotOne
                End If
                 If sTest Like Sheet2.Range("b11").Value Then
                    Range("A" & c.Row) = "Yes"
                    GoTo GotOne
                End If
                 If sTest Like Sheet2.Range("b12").Value Then
                    Range("A" & c.Row) = "Yes"
                    GoTo GotOne
                End If
                 If sTest Like Sheet2.Range("b13").Value Then
                    Range("A" & c.Row) = "Yes"
                    GoTo GotOne
                End If
                 If sTest Like Sheet2.Range("b14").Value Then
                    Range("A" & c.Row) = "Yes"
                    GoTo GotOne
                End If
                 If sTest Like Sheet2.Range("b15").Value Then
                    Range("A" & c.Row) = "Yes"
                    GoTo GotOne
                End If
                 If sTest Like Sheet2.Range("b16").Value Then
                    Range("A" & c.Row) = "Yes"
                    GoTo GotOne
                End If
                 If sTest Like Sheet2.Range("b17").Value Then
                    Range("A" & c.Row) = "Yes"
                    GoTo GotOne
                End If
                 If sTest Like Sheet2.Range("b18").Value Then
                    Range("A" & c.Row) = "Yes"
                    GoTo GotOne
                End If
                 If sTest Like Sheet2.Range("b19").Value Then
                    Range("A" & c.Row) = "Yes"
                    GoTo GotOne
                End If
                 If sTest Like Sheet2.Range("b20").Value Then
                    Range("A" & c.Row) = "Yes"
                    GoTo GotOne
                End If
                 If sTest Like Sheet2.Range("b21").Value Then
                    Range("A" & c.Row) = "Yes"
                    GoTo GotOne
                End If
                 If sTest Like Sheet2.Range("b22").Value Then
                    Range("A" & c.Row) = "Yes"
                    GoTo GotOne
                End If
                 If sTest Like Sheet2.Range("b23").Value Then
                    Range("A" & c.Row) = "Yes"
                    GoTo GotOne
                End If
                 If sTest Like Sheet2.Range("b24").Value Then
                    Range("A" & c.Row) = "Yes"
                    GoTo GotOne
                End If
                
GotOne:
            Next x
        Next c
    Next i
End Sub

This seems to work as I need it to, but, my list on Sheet2 is growing and I wonder if there is a better way for me to loop through all the entries of the sheet2 range (that will be named, i.e. SearchTermsList) more efficiently? as writing an entry per cell in that range will take me ages and the range can often change.

i.e. for each row in sheet1.range(2:last row), if cells G or H contain within them any of the entries of sheet2.range(SearchTermsList), mark up sheet1.range("A"&row) as "Yes" - search for anything in the search list vs specifying every cell within the search list... if that makes sense?

Thanks, Upex

 

[Snakehips]

@Upex maybe like below?

Sub highlight_matches_found_in_list()

    Dim r As Range
    Dim c As Range
    Dim i As Integer
    Dim x As Integer
    Dim sColumn As String
    Dim vSplit As Variant
    Dim sTest As String
    For i = 1 To 2
        If i = 1 Then
            sColumn = "G"
        Else
            sColumn = "H"
        End If
        Set r = Range(Range(sColumn & 2), Range(sColumn & Sheet1.Rows.Count).End(xlUp))
        For Each c In r.Cells
            vSplit = Split(c, " ")

            For x = LBound(vSplit) To UBound(vSplit)
                sTest = Replace(vSplit(x), ",", "")
                
                For Each testc In Sheet3.Range("SearchTermsList")
                    If sTest Like testc.Value Then
                        Range("A" & c.Row) = "Yes"
                        GoTo GotOne
                    End If
                     
                Next testc
GotOne:
            Next x
        Next c
    Next i
End Sub

 

[Upex]

Many thanks Snakehips - that appears to have worked a charm and way more simple than the road I was trying to head down - KISS eh!

Thanks once again, much appreciated.

 

[Upex]

Separate, but directly driven from the above, so trying here vs a new post... I've noticed that the above code is not flagging for phrases, only single words. I.e. if the search list says 'price' as one of its terms, it flags when it finds G or H contain 'price'. But if the search list entry is 'best price' it's not being flagged when the G or H cell contains 'best price' within its text.

Anyone know how to amend the search code, or perhaps how I can enter the > 1 word search phrase within the listing differently, so as to make them pick up (next to each other as per the search listing - like making it see the search listing contents as one string of the two words and the space combined - I'm not after it searching for best and then price separately)?

Thanks, Upex