Error Cell "#N/A"

[Len]

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

 

[Bob Phillips]

For Each cell In Activesheet.UsedRange

If cell.text="#N/A" Then

cell.Value = 0
cell.NumberFormat = "0.00"
End If
Next cell

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



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

 

[JW]

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",.....)

 

[Rick Rothstein \(MVP - VB\)]

How to set VB code to search the entire active worksheet 1 for error

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(xlCellTypeFormulas, xlErrors)
On Error GoTo 0
If Not R Is Nothing Then
R.Value = 0
R.NumberFormat = "0.00"
End If

Rick

 

[Rick Rothstein \(MVP - VB\)]

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(xlCellTypeFormulas, xlErrors)
On Error GoTo 0
If Not R Is Nothing Then
R.Value = 0
R.NumberFormat = "0.00"
End If

Ignore this code... it will change ALL error cells to 0.00, not just #N/A
errors.

Rick

 

[JW]

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(xlCellTypeFormulas, xlErrors)
On Error GoTo 0
If Not R Is Nothing Then
  R.Value = 0
  R.NumberFormat = "0.00"
End If

Rick- Hide quoted text -

- Show quoted text -

Rick, that is the way I was going to suggest doing it as well, but he
mentioned that this was only for the #N/As, so I was assuming that he
could possibly have other errors in the sheet. Without the loop, it
would change all of the errors to 0.00, not just the #N/As.

 

[Rick Rothstein \(MVP - VB\)]

Rick, that is the way I was going to suggest doing it as well, but he

mentioned that this was only for the #N/As, so I was assuming that he
could possibly have other errors in the sheet. Without the loop, it
would change all of the errors to 0.00, not just the #N/As.

Yes, that eventually dawned on me... your message and my correction message
"crossed in the mail".

Rick

 

[Len]

Yes, that eventually dawned on me... your message and my correction message
"crossed in the mail".

Rick

Hi ,

Thanks all of you, it works

Regards
Len