How to check if a file contains a string?

Basically, I want to perform "Find ... within Workbook".

When I write the VBA code, do I have to loop through each sheet?

Thanks.

Originally Posted by Leith Ross

Hello niuyuer,

Do you want to find only the first occurrence of the string on a worksheet or all of occurrences on all sheets?
Are you searching only the cells' values, formulas, or both?
What do you want to do once a string is found?

The answers to these questions impact how your VBA code needs to be written.

Thanks.

Search cell's values. Actually, search some cells' value in file A, and see if File B contains those values. To me, it does not matter it is first occurrence or not, all I want to know is if it is included in File B.

But the difficulty is: for example, I have value "ABC" in File A, and would like to know if file B contains ABC. I can search file B, the issue is if File B contains "ABC*", the code will consider that File B contains "ABC". This is not what I want. Let me be specific, let us say file B contains (XYZ + ABCD)/2 > 0, the answer I like to get is "File B does not contain ABC", since ABCD is different from ABC. This is the difficult part to turn into code, I know how to write the code to show (XYZ + ABCD)/2 > 0 containing ABC, but this is just not accurate.

Actually, I have the rule to determine, if I want to know if file B contains ABC, and if I find ABC* in file B and the character immediately following C is also a letter (what I mean letter is: A, B, C, ...., X, Y, Z), then this is not what I am looking for.

So how can I know if a particular character in a string is a letter or a number? There should be the only two cases, the string I want to search can be either letter only, or letter & number mixed, not other symbols.

Here are desire output results:

If search ABC: ABCF does not contain ABC, ABC4 does not contains ABC.

If search A20: A205 does not contain A20.

Originally Posted by Leith Ross

Hello niuyeur,

Here are two macros that work together to find search terms in workbook "A" in workbook "B". The search terms in workbook "A" are pulled from the cell values in column "A" starting with cell "A1" on "Sheet1".

Each sheet in workbook "B" is searched for each term from workbook "A". When found, a message box appears informing you which term was found and the name of the workbook it was found in.

Add a new standard Module to your VBA project. Copy and paste this code into it. Put your search terms in column "A" of "Sheet1". Run the macro FindInWorkbook. All open workbooks, except the workbook with the macro, will be searched.

Please Login or Register  to view this content.

Thanks much, actually, I can write on my own.

Let us say, cell A1 is "ABC", cell A2 is "GABCD DEF ABC".

Cell A2 contains two ABC, first ABC starts at position 2, second ABC starts at position 11.

So how should I write VBA to get the numbers 2 and 11?

Thanks.

Thank you so much.