Add an 'x' to column U if

[Greenal]

Hello,

After a fair bit of searching i'm unable to find / alter a suitable macro.

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')

Would much prefer a macro as there are already a number of formulas involved.

Many thanks in advance

[Bob Phillips]

Please Login or Register  to view this content.

[Greenal]

Hi Bob,

Many thanks for such as quick reply.

I've looked through the code you supplied - would it be possible for you to add my three examples into the code so i can get an understanding of the 'template' i need to use?

Thanks again.

[Bob Phillips]

Change the constants LOOK_FOR_1 and LOOK_FOR_2

[Greenal]

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)?

[Bob Phillips]

You probably need to state your EXACT requirements.

[Greenal]

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

[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)?

[Greenal]

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?

[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?

[Greenal]

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]

Please Login or Register  to view this content.

Use F8 to step through the code.

[Greenal]

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

Please Login or Register  to view this content.

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

Thanks again

[snb]

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

[Greenal]

Thanks snb.

Tried again, but got another error.

------------
Run-time error '9':

subscript out of range
-----------

and it highlighted on debug, this:

Please Login or Register  to view this content.

[snb]

su(jj,1) = "x"

[Greenal]

Works great now

Many thanks for your assistance - greatly appreciated

1/ Ah, when i run the macro 1st time it works perfect, but then if existing text is altered and the macro runs again, it isn't 'working' / updating the 'x's in the U column...

Not sure if i'm moving the goalposts here, but is it possible for the macro to take a 'fresh' as is look at the data when it runs?

[snb]

The only 'connection' between 'fisrt run' and 'second run' is the content of the worksheet range("A:O").
Show in a sample workbook what you mean...

[Greenal]

The only 'connection' between 'fisrt run' and 'second run' is the content of the worksheet range("A:O").
Show in a sample workbook what you mean...

Tricky to show in a workbook because you change the data.

Say if you have:
'hello' in B2
'goodbye' in C2

and the macro runs it will insert the 'x' into U2

But...if you edit B2 to read 'hell' and re-run the macro, the 'x' remains in U2.

(I can certainly upload a workbook if still required )

[snb]

Please Login or Register  to view this content.

[Greenal]

That code is now updating the edited data

Many thanks for your help snb!