Macro to clear contents of certain cells

[MrAcquire]

I have a range of cells that contains mostly numeric values but some cells
are populated with an alpha dash (-). I would like to clear the contents of
only those cells that contain a dash and leave the other cells untouched.

Replace won't work because it replaces a dash with an alpha blank cell and
the cell contents need to be clear or numeric to perform sumproduct()
calculations on them or else it results in a #VALUE! error. Yes, I know I
could replace "-" with 0, but I need the cell to be blank (no contents). I
assume a macro is required. Any ideas?

 

[Gary''s Student]

Sub ClearDash()
Dim s As String
s = "-"
For Each r In ActiveSheet.UsedRange
If InStr(r.Value, s) > 0 Then
r.Clear
End If
Next
End Sub

 

[MrAcquire]

Thanks. Your macro achieves the desired result except that I had to modify
it to ClearContents instead of just Clear so it wouldn't clear the formatting
(see below)

Sub ClearDash()
Dim s As String
s = "-"
For Each r In ActiveSheet.UsedRange
If InStr(r.Value, s) > 0 Then
r.ClearContents
End If
Next
End Sub

But the macro results in a visual basic "Type mismatch" error when the macro
is finished. I just click OK and everything seems to have worked but macros
should not result in errors. Any idea what's causing this and how to fix it?

 

[MrAcquire]

Thanks. I changed r.Clear to r.ClearContents and it worked as desired
(otherwise it clears the formatting, too). But the macro is resulting in a
visual basic Type Mismatch error when it's through. I click OK and the macro
appears to have worked but macros shouldn't result in error messages. Any
idea what's causing this error and how to fix it?

 

[John Blancuzzi]

The problem is that by using "r.value" you are saying that the variable r is a number. Since you are trying to find "-" in the string and "-" is not a number yu will get the type mismatch error.

To resolve this declare r as a Range and then use "r.Text" to avoid the error.

Here is my code:

Sub ClearRefError()
Dim s As String
Dim r As Range
s = "REF"
For Each r In ActiveSheet.UsedRange
If InStr(r.Text, s) > 0 The
r.ClearContents
End If
Next
End Sub

Works like a champ in Excel 2010.