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?

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

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 -

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 -

On Tue, 16 Feb 2010 19:10:54 -0800 (PST), mracquire
wrote:

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.

As I wrote, one way would be to use the Text property of the range object,
instead of the Value property. Sorry I didn't give an example, but here it is:

====================================
Option Explicit
Sub ClearContentsX()
Dim s As String
Dim r As Range

s = "x"
For Each r In Selection
If r.Text = s Then
r.ClearContents
End If
Next r

End Sub
=========================
--ron

Why not take the easy way out and let Excel do all the work for you...

Sub ClearXs()
Selection.Replace "x", "", xlWhole, , False
End Sub

--
Rick (MVP - Excel)


"mracquire" wrote in message
...
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 -

On Wed, 17 Feb 2010 01:37:15 -0200, "Joe" wrote:

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

Your solution should work if it is the case that all possible errors should be
ignored. I prefer to avoid error handlers if there are simpler ways of doing
things; that's why I would probably just use the Text property of the Range
object.
--ron

On Tue, 16 Feb 2010 22:56:28 -0500, "Rick Rothstein"
wrote:

Why not take the easy way out and let Excel do all the work for you...

Sub ClearXs()
Selection.Replace "x", "", xlWhole, , False
End Sub

--
Rick (MVP - Excel)

Too simple and fast :-)

Nice one-liner to accomplish the same thing as the loop.
--ron

Excellent! Thanks, Ron. So much to learn. Thanks for your patience.

MrA

On Feb 16, 9:52*pm, Ron Rosenfeld wrote:
On Tue, 16 Feb 2010 19:10:54 -0800 (PST), mracquire
wrote:

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.

As I wrote, one way would be to use the Text property of the range object,
instead of the Value property. *Sorry I didn't give an example, but here it is:

====================================
Option Explicit
Sub ClearContentsX()
*Dim s As String
*Dim r As Range

s = "x"
For Each r In Selection
* * If r.Text = s Then
* * * * r.ClearContents
* * End If
Next r

End Sub
=========================
--ron

On Tue, 16 Feb 2010 20:07:27 -0800 (PST), mracquire
wrote:

Excellent! Thanks, Ron. So much to learn. Thanks for your patience.

MrA

Glad to help. Thanks for the feedback. See Rick's posting for a different
approach.
--ron