Look for several words in string

[Fredrik1987]

Hi!

I've been trying to solve this problem for a couple of days now without any luck.
I wnat to search a range of cells based on their values. But somehow the instr() function wont work, I've used it earlier with sucsess, so I don't understand why it wont work now.

So far I'm just testing the code, so the words I want to look for are inserted into "A1" and search through "A2:A6". As it is now, using instr() inserts "OK" into all the cells in column B.


This is how the sheet looks like:

Column A	Column B	Column C
YES, NO		YES
YES		NO
yes
no
maybe
well yes

<tbody>
</tbody>

Sub Test()
' VIRKER
Dim rSearch_Target As Range
Dim cell As Range
Dim S
Dim N

Dim i As Integer
Dim wordcount As Integer
Dim iLastRow_Søkeord As Integer

Dim sWords_To_Search_For As String
Dim vArray_SearchWords As Variant
Dim sArray_Search_Words

Set rSearch_Target = Sheets("Sheet1").Range("A1")

'' Number of words to search for, used to redim the size of the search array.
For Each cell In rSearch_Target
S = Application.WorksheetFunction.Trim(cell.Text)
N = 0
    'Hvis S ikke er tom then
    If S <> vbNullString Then
    N = Len(S) - Len(Replace(S, " ", "")) + 1
    End If
' Antall ord i søkestrengen
wordcount = wordcount + N
Next

'' Search range and search array, split search words with comma (,)
sWords_To_Search_For = Sheets("Sheet1").Range("A1").Value
vArray_SearchWords = Split(sWords_To_Search_For, ", ")
iLastRow_Søkeord = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

ReDim sArray_Search_Words(0 To wordcount)

'' This part is just to verify that the array is correct, so far the script works just fine
For i = 0 To UBound(vArray_SearchWords)
    sArray_Search_Words(i) = vArray_SearchWords(i)
    Cells(i + 1, "C").Value = sArray_Search_Words(i)
Next i
 

For Each cell In Range("A2:A" & iLastRow_Søkeord)
    For i = 0 To wordcount
    ' Here's the problem, earlier I've just the exact line, and it works just fine, but somehow it finds that all the cells contain my search strings
    ' If I ommit Instr() it works just as planned
    If sArray_Search_Words(i) = cell.Value _
        Or InStr(1, cell.Value, sArray_Search_Words(i)) > 0 Then
            cell.Offset(, 1).Value = "OK"
        End If
    Next i
Next
End Sub

I've tried different functions without any luck, I've used the instr() function the EXACT same way earlier and it works perfectly.

 

[Fredrik1987]

I should clarify the comment inside the last loop: It works just fine if I only use sArray_Search_Words(i) = cell.value, but some of the cells contains strings, hence the instr() function...

 

[Fredrik1987]

Finally found an answer, posting the solution below.

Option Compare Text
Sub Test()

Set rSearch_Target = Sheets("Ark1").Range("A1")
vArray_Søkeord = Split(rSearch_Target, ", ")

'' Antall søkeord (Wordcount)
For Each cell In rSearch_Target
S = Application.WorksheetFunction.Trim(cell.Text)
N = 0
    'Hvis S ikke er tom then
    If S <> vbNullString Then
    N = Len(S) - Len(Replace(S, " ", "")) + 1
    End If
' Antall ord i søkestrengen
wordcount = wordcount + N
Next

Set rKommentar = Range("A1:A6")

' For hver celle i søkeområdet, let gjennom hver celle
For Each cell In rKommentar
    For i = 0 To wordcount - 1
        If InStr(cell.Value, vArray_Søkeord(i)) > 0 Then
        End If
    Next i
Next

End Sub

 

[Fredrik1987]

A small add in if anyone finds this post later, the script above only works if the search string contains two words. My mistake...
The script should (as far as I can tell) work now

Sub Searh_String()


Set rSøkeord_i_saken = Range("A1") 'Cell that contains search phrases
Set range1 = Range(Searh_range1) 'First range you want to search in
Set range2 = Range("A10:A15") 'Second range you want to search in

'' Searches through the range1 and range2, it is possible to expand this further if you'd like
'' By default the splits the search string using ",": "yes, no" = [yes no]
Set rSøk_i_range = Union(range1, range2)
vArray_søkeord = Split(rSøkeord_i_saken, ", ")

    For Each cell In rSøk_i_range
        For i = 0 To UBound(vArray_søkeord)
        Cells(i + 1, "C").Value = vArray_søkeord(i)
            If InStr(cell.Value, vArray_søkeord(i)) > 0 Then
                cell.Offset(, 1).Value = "OK"
                cell.EntireRow.Hidden = False
                Exit For
            Else
                cell.EntireRow.Hidden = True
            End If
        Next i
    Next
End Sub