Add an 'x' to column U if

Public Sub CountWords()
Const FORMULA_FIND As String = _
    "=SUMPRODUCT(--(ISNUMBER(FIND(""<word>"",A<row>:O<row>))))"
Const LOOK_FOR_1 As String = "hello"
Const LOOK_FOR_2 As String = "goodbye"
Dim Lastrow As Long
Dim i As Long
Dim total As Long

    Application.ScreenUpdating = False
    
    With ActiveSheet

        For i = 1 To .UsedRange.Rows.Count
        
            total = total + Application.Evaluate(Replace(Replace(FORMULA_FIND, _
                                                            "<word>", LOOK_FOR_1), _
                                                    "<row>", i)) _
                          + Application.Evaluate(Replace(Replace(FORMULA_FIND, _
                                                            "<word>", LOOK_FOR_2), _
                                                    "<row>", i))
        Next i
    End With
    
    MsgBox total
    Application.ScreenUpdating = True
End Sub

Change the constants LOOK_FOR_1 and LOOK_FOR_2

Originally Posted by Bob Phillips

Change the constants LOOK_FOR_1 and LOOK_FOR_2

Ah right, thanks.

Would this mean that i would need to have a separate macro for each 'hello' and 'goodbye' (each entry of terms that i'm seeing if they appear in the same row)?

You probably need to state your EXACT requirements.

Originally Posted by Bob Phillips

You probably need to state your EXACT requirements.

As per opening post:

Aim:
- Look at A2:O2 and if 'this word or words' AND 'this word or words' appear in any cell then add an 'x' to U2.

Misc:
- spread sheet has thousands of rows
- there will be a number of terms to search for. Such as ('hello' AND 'goodbye', 'red' AND 'blue, 'blue' AND 'water')

Further info to clarify:

I need the code to look at each row for certain terms. The number of these terms are unknown at the moment.

When i refer to terms i mean if 'word a' AND 'word b' appear in a row (columns A to O) then add an 'x' to column U on the same row as the terms appeared.

Real life scenario: If the words 'hello' and 'goodbye' appear in any cells on one row then input an 'x' in the U column of that row.

Hope that helps and is clear enough - many thanks

I don't see that explaining this statement in your previous post ... Would this mean that i would need to have a separate macro for each 'hello' and 'goodbye' (each entry of terms that i'm seeing if they appear in the same row)?

Originally Posted by Bob Phillips

I don't see that explaining this statement in your previous post ... Would this mean that i would need to have a separate macro for each 'hello' and 'goodbye' (each entry of terms that i'm seeing if they appear in the same row)?

Hi Bob,

By this I was meaning would i need to have a separate saved macro for each 'term' and 'term2' that i was querying.

So in effect i was asking could all the possible 'term' and term2', 'term3' and 'term4', 'term5' and term6' be controllable from just a single macro run once as opposed to your previous code which to the untrained eye looked as if it handled one query at a time?

Hope that helps?

My code looks for word pairs, which is how I read your original requirement. I am still not sure if you want to look for a number of word pairs in separate queries, or any number of words in a single query. And how does this procedure get told what words to search for?

Originally Posted by Bob Phillips

My code looks for word pairs, which is how I read your original requirement. I am still not sure if you want to look for a number of word pairs in separate queries, or any number of words in a single query. And how does this procedure get told what words to search for?

Hi Bob,

- yes, want to look for a number of independent word pairs in one query / macro
- i could add a list of the word pairs into the code as and when more are needed to be added or make the macro look at a table which is populated with word pairs? Either would be great.

Many thanks.

snb thank you also for adding to the thread.

I've just ran the macro you have provided, but it's turned up the following error:
-------------
Compile error:
Syntax error
-------------
whilst highlighting this line of code

sp=split(sq(j,"|")

Is it something i'm doing wrong or is it the code?

Thanks again