Macro for removing error values in cells.

[Heera]

Hi,

I have a set of data in column H and most of the cells has the error
value "#N/A".
I want to replace the error value from "#N/A" to "-"(Dash).
Following is the code but I am getting a error (Type Mismatch) while
running the code.

Range("H5").Select

Do Until ActiveCell.Value = ""
If ActiveCell.Value = "#N/A" Then
ActiveCell.Value = "-"
Selection.Offset(1, 0).Select
End If
Selection.Offset(1, 0).Select
Loop

Can someone suggest me the code which can replace the error values.

Regards
Heera Chavan

 

[Dave Peterson]

You can use
if activecell.text = "#N/A" then

Hi,

I have a set of data in column H and most of the cells has the error
value "#N/A".
I want to replace the error value from "#N/A" to "-"(Dash).
Following is the code but I am getting a error (Type Mismatch) while
running the code.

Range("H5").Select

Do Until ActiveCell.Value = ""
If ActiveCell.Value = "#N/A" Then
ActiveCell.Value = "-"
Selection.Offset(1, 0).Select
End If
Selection.Offset(1, 0).Select
Loop

Can someone suggest me the code which can replace the error values.

Regards
Heera Chavan

 

[Michael]

First you establish your range something like this:

iLastrow = Range("H65536").End(xlUp).Row
Set Rng = Range("H5:H" & iLastrow)
Then Establish the loop and activate cell:
For Each Rng In Rng

Rng.Activate

If IsError(ActiveCell.Value) Then
errval = ActiveCell.Value
Select Case errval
Case CVErr(xlErrDiv0)
MsgBox "#DIV/0! error"
Case CVErr(xlErrNA)
MsgBox "#N/A error"
Case CVErr(xlErrName)
MsgBox "#NAME? error"
Case CVErr(xlErrNull)
MsgBox "#NULL! error"
Case CVErr(xlErrNum)
MsgBox "#NUM! error"
Case CVErr(xlErrRef)
MsgBox "#REF! error"
Case CVErr(xlErrValue)
MsgBox "#VALUE! error"
End Select
End If
Next
Replace the MsgBox for:
ActiveCell.Value = "-"
or for anythingelse you may want