Search Cell Range For Words In List

Re: Search A Cell For Mutilple Text Values

Welcome to Oz.

Please workbooks, not pictures, if you want help with Excel.

[COLOR="Blue"]=IF( OR( ISNUMBER( SEARCH(wordList, A1) ) ), -1, 0)[/COLOR]

This is an array formula, meaning it MUST be confirmed with Ctrl+Shift+Enter, not just Enter. (With the cursor in the formula bar, hold down Ctrl and Shift, then press Enter.) You'll know you did it correctly if curly braces appear around the formula in the Formula Bar; you cannot type in the braces directly.

Change SEARCH to FIND if you want a case-sensitive match.

Re: Search A Cell For Mutilple Text Values

This assumes keywords in F1 and down, values in A1 and down. Come back if it doesn't do what you want.

Sub x()


Dim rng As Range, i As Long, keys


Range("F1", Range("F1").End(xlDown)).Name = "Keywords"
keys = Range("Keywords").Value
For Each rng In Range("A1", Range("A1").End(xlDown))
    For i = LBound(keys, 1) To UBound(keys, 1)
        If InStr(rng, keys(i, 1)) > 0 Then
            rng.Offset(, 1) = -1
            GoTo line0
        Else
            rng.Offset(, 1) = 0
        End If
    Next i
line0:
Next rng


End Sub

Ho hum...much simpler shg!

Re: Search A Cell For Multiple Text Values

Quote

VB Method
...
• Case sensitive

Could easily be made case insensitive

Quote

Method with Built in excel functions
...
• MUST push F4 after wordlist

Not if you use a named range.

Quote from StephenR

Ho hum...much simpler shg!

Yes, but if you needed to do this in VBA, I don't think you can do it without a loop as you did, even though Search and Find are available in WorksheetFunction. I just looked at that a day or two ago. If you figure out how, let me know!

Re: Search A Cell For Multiple Text Values

try

Sub test()
Dim a, i As Long, myPtn As String
With Sheets("sheet1")
    a = .Range("a23", .Range("a" & Rows.Count).end(xlUp)).Resize(,2).Value
End With
ReDim b(1 To UBound(a,1), 1 To 1)
With Sheets("sheet2")
    myPtn = Join$(Application.Transpose(.Range("[B][COLOR="DimGray"]AddressHere[/COLOR][/B]").Value), "|")
    '                                             ↑ Key Word Search Address here
End With
With CreateObject("VBScript.RegExp")
    .Pattern = "(" & myPtn & ")"
    .IgnoreCase = True
    For i = 1 To UBound(a,1)
        a(i,2) = .test(a(i,1))
    Next
End With
Sheets("sheet1").Range("a23").Resize(UBound(a,1), 2).Value = a
End Sub