Trouble with Clear Contents Macro

[mracquire]

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 A14 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 A14 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?

 

[Ron Rosenfeld]

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 A14 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 A14 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

 

[mracquire]

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.

 

[Joe]

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" <[email protected]> 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.

 

[Ron Rosenfeld]

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

 

[Rick Rothstein]

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)


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.

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

Too simple and fast

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

 

[mracquire]

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

MrA

 

[Ron Rosenfeld]

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