Macro for removing error values in cells.

H

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
 
D

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
 
M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top