On Tue, 16 Feb 2010 17:55:35 -0800 (PST), mracquire
wrote:

I have a worksheet that contains numbers, text, blank cells, a text
entry of x, and #N/A entered as =na(), i.e. an error value. I would
like to select (highlight) a range, say A1:D4 and run a macro that
clears the contents of only those cells whose value is x. See sample
worksheet below.

A B C D
1 400 x #N/A text1
2 x text2 200
3 text3 300 x
4 #N/A x text2 500
5
6 text2 20 10
7 - text3 #N/A 500
8 - 300 text1

When I select A1:D4 and run the following macro, it works except for
one detail that I can't figure out. It clears not only the contents
of those cells containing only an x but those cells containing #N/A as
well! I would like the macro to not clear the #N/A cells nor any
other cells unless they contain only an x.

Sub ClearContentsX()
On Error Resume Next
Dim s As String
s = "x"
For Each r In Selection
If r.Value = s Then
r.ClearContents
End If
Next r
End Sub

What am I doing wrong?

You are trying to compare an error value to a string. Ordinarily, VBA would
give you a type-mismatch error. However, you have an On Error Resume Next
statement preceding, so when VBA encounters the Type Mismatch error, it ignores
the error and executes the next statement, which is your "clear contents"
statement.

I don't know why you have the On Error Resume Next, but I would suggest that
should not be used until you have eliminated all the errors in your routines.

There are many workarounds. One would be to use the "Text" property of the
range object to compare with "s".

And eliminate the On Error statement unless you really need it.

Also, I would suggest using the Option Explicit statement routinely. If you
check Tools/Options and Select "Require Variable Declarations". As a matter of
fact, I have everything checked on the Editor tab.

--ron