Follow Up Macro Question

[MrAcquire]

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?

 

[Mike H]

Hi,

Include this as the first line of your sub routine

On Error Resume Next


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Dave Peterson]

You could check for an error:

For Each r in ActiveSheet.UsedRange
if iserror(r.value) then
'skip it???
else
If r.Value = s Then
r.ClearContents
End If
end if
Next r

Or you may be able to test the string you see in the cell:

For Each r in ActiveSheet.UsedRange
If r.Text = s Then
r.ClearContents
End If
Next r

 

[MrAcquire]

I like simple and this solution works perfectly. Thanks for the help, Mike.

 

[Gord Dibben]

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