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

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)



"Len" wrote in message
...
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

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

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

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

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

Rick

On May 19, 11:54Â*am, "Rick Rothstein \(MVP - VB\)"
wrote:
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- 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, 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

On May 20, 12:16*am, "Rick Rothstein \(MVP - VB\)"
wrote:
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

Hi ,

Thanks all of you, it works

Regards
Len