Testing each cell for error

  • Thread starter Thread starter psatish
  • Start date Start date
P

psatish

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!
 
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
 
Back
Top