Testing each cell for error
 

Hey guys,

I need a little help.

I have a spreadsheet with a bunch of #N/A, #VALUE! and #DIV/0! errors.
I want to be able to check each cell in a sheet and if a cell is an
error, replace the contents with the string "NA".

I keep getting type mismatch errors though. Not sure how I'm supposed
to approach this. I declared a testValue variable as double (Dim
testValue As Double) and set it equal to the first cell in the
worksheet I want to test. My thinking was that I would make a loop
with an if statement inside it to go through and test each cell, and
replace the contents if an error is found.

However, when I get to the cells that contain errors, I get a type
mismatch error in VBA.

Should I not be declaring the test variable as double? Is there
something else? Not sure how to go about this, any help would be
greatly appreciated.

Thanks!

Testing each cell for error
 

one way

Sub findErrors()
Dim cell As Range
For Each cell In Worksheets("sheet1").UsedRange
If IsError(cell.Value) Then
cell.Value = "N/A"
End If
Next

End Sub

--


Gary


wrote in message
ps.com...
Hey guys,

I need a little help.

I have a spreadsheet with a bunch of #N/A, #VALUE! and #DIV/0! errors.
I want to be able to check each cell in a sheet and if a cell is an
error, replace the contents with the string "NA".

I keep getting type mismatch errors though. Not sure how I'm supposed
to approach this. I declared a testValue variable as double (Dim
testValue As Double) and set it equal to the first cell in the
worksheet I want to test. My thinking was that I would make a loop
with an if statement inside it to go through and test each cell, and
replace the contents if an error is found.

However, when I get to the cells that contain errors, I get a type
mismatch error in VBA.

Should I not be declaring the test variable as double? Is there
something else? Not sure how to go about this, any help would be
greatly appreciated.

Thanks!

Testing each cell for error
 

That works, thanks :)