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?

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.


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

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

--

Dave Peterson

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

"Mike H" wrote:

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.


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

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?