Macro for removing error values in cells.
 

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

Macro for removing error values in cells.
 

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



Heera wrote:

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

Macro for removing error values in cells.
 

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
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Heera" wrote:

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