On Tue, 30 Jun 2009 13:33:01 -0700, J741
wrote:

I traced this to a cell which contains the following text:
#NAME?

You really need to better define what we are dealing with.

In your original post, you indicated:

"... contain sentences, phrases, or paragraphs of comments
entered by users."

#NAME? errors are the result of someone trying to enter a FORMULA. I did not
include FORMULA's in the class of "sentences, phrases, or paragraphs of
comments".

Now a problem is that you have not defined what you want to do in the event
your user enters something that evaluates to a formula?

Are formulas legitimate entries for a user, or are they always going to be a
mistake?

If they are legitimate entries, then you probably want to flag those that
result in errors but test the results of legitimate formulas.

If they are NEVER going to be legitimate entries, then you can test the formula
text (and not the formula result) if there is a formula in the cell, regardless
of whether it returns an error value or not.

If your users will NEVER be entering formulas, then each entry that evaluates
to a formula is really a comment that happens to begin with an equal sign.

That being the case, one solution is to test each cell to see if it is a
formula, and, if so, evaluate the formula text. This approach would ignore the
"equal" sign since it is only one character in length (and you want legitimate
words to be more than three characters in length). However, since, under the
assumption that "=" represents a token for the word "equal" or "equals", maybe
we should substitute that word for the token.

Having done this, you may still miss some entries as formulas that don't
evaluate to errors may have the word-defining spaces removed by Excel's formula
parser.

But if formulas are legitimate user-entries, then you need to use a different
approach.

I'm not having much fun with this code, as I keep running into error that I
don't fully understand.

Feel free to try different code in order to have fun.

Or, if you want to work with this code, post back regarding my questions about
user-entered formulas in these cells, and we can devise code to handle the
problem.
--ron