You check to see if r has a formula.

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


Gord Dibben MS Excel MVP

On Thu, 11 Feb 2010 23:18:01 -0800, MrAcquire
wrote:

Earlier, I asked about writing a macro to clear the contents of cells whose
value was an alpha dash (-) without changing values or formats of the other
cells. Based on expert feedback, I came up with the following solution.
Highlight the desired range and run the following macro.

Sub ClearDash()
Dim s As String
s = "-"
For Each r in ActiveSheet.UsedRange
If r.Value = s Then
r.ClearContents
End If
Next
End Sub

This works just fine unless a cell in the range contains an error value such
as #N/A in which case the macro aborts at that point with a "Type mismatch"
error. I have been unsuccessful at figuring out a way to skip over any cell
in the range that contains an error value. I thought of maybe using the
following somewhere in the code but I can't get it to work.

If Application.ErrorCheckingOptions.EvaluateToError = True Then

Any ideas on how I can modify the above macro?