On May 19, 10:59*am, Len wrote:
Hi,
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 ” ?
Please help, thanks
Regards
Len
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
Of course, the best solution would be to have your formula not produce
an error. You could do something like this:
=IF(ISNA(VLOOKUP(......)),"0.00",.....)