View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Rick Rothstein \(MVP - VB\)[_1963_] Rick Rothstein \(MVP - VB\)[_1963_] is offline
external usenet poster
 
Posts: 1
Default Error Cell "#N/A"

How to set VB code to search the entire active worksheet 1 for error
cell “ #N/A “ and replace one or more error cells “ #N/A” with value
cell “ 0.00 ” ?


I'm assuming that you are looking for cells containing formulas that
are producing the #N/A, correct? If so, something like this will loop
through all of the cells in the worksheet that contain errors and, if
the error is of the #N/A type, will change their values to "0.00".
This will skip all other errors such as #NAME! and #REF.

Sub sample()
Dim r As Range
For Each r In Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
If Application.WorksheetFunction.IsNA(r) Then _
r.Value = "0.00"
Next r
End Sub


It looks like you might be able to do that without the loop...

Dim R As Range
On Error Resume Next
Set R = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las, xlErrors)
On Error GoTo 0
If Not R Is Nothing Then
R.Value = 0
R.NumberFormat = "0.00"
End If

Rick