Hi Ron

If the error was the same, you could make the following changes:

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


Sorry for entering the discussion between both of you.

Bye.

Joe



"mracquire" escreveu na mensagem
...
I'm a VBA newbie so bear with me. I originally excluded On Error
Resume Next and got the type mismatch error that you described. But I
don't know what to include in my code instead to accomplish my
objective. Your error checking suggestions are good, but I need
assistance in modifying my macro. That's why I included a sample
worksheet and copy of my macro.

On Feb 16, 8:26 pm, Ron Rosenfeld wrote:
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 x 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- Hide quoted text -

- Show quoted text -