#N/A in 150 WS
 

hi:

I have a wb with 150 ws. some cell in some ws have an #N/A and ther
is no formula in the cell. how can I replace the # with a blank o
just plainly turn that cell value to zero. I have way to many ws to d
it by hand.


any help is greatly appreciated

--
halem
-----------------------------------------------------------------------
halem2's Profile: http://www.excelforum.com/member.php...nfo&userid=993
View this thread: http://www.excelforum.com/showthread.php?threadid=55749

#N/A in 150 WS
 

Try some code like the following

Sub AAA()
Dim Rng As Range
Dim WS As Worksheet
On Error Resume Next
For Each WS In Worksheets
For Each Rng In ActiveSheet.UsedRange.Cells
If Rng.Value = CVErr(xlErrNA) Then
Rng.Value = 0
End If
Next Rng
Next WS
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"halem2"
wrote in message
...

hi:

I have a wb with 150 ws. some cell in some ws have an #N/A and
there
is no formula in the cell. how can I replace the # with a
blank or
just plainly turn that cell value to zero. I have way to many
ws to do
it by hand.


any help is greatly appreciated.


--
halem2
------------------------------------------------------------------------
halem2's Profile:
http://www.excelforum.com/member.php...fo&userid=9930
View this thread:
http://www.excelforum.com/showthread...hreadid=557492

#N/A in 150 WS
 

Another way:

Option Explicit
Sub testme()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.Cells.Replace What:="#n/a", Replacement:="", _
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False
Next wks
End Sub

Replacement:=""
could be
Replacement:="0"

if you wanted 0's.


halem2 wrote:

hi:

I have a wb with 150 ws. some cell in some ws have an #N/A and there
is no formula in the cell. how can I replace the # with a blank or
just plainly turn that cell value to zero. I have way to many ws to do
it by hand.

any help is greatly appreciated.

--
halem2
------------------------------------------------------------------------
halem2's Profile: http://www.excelforum.com/member.php...fo&userid=9930
View this thread: http://www.excelforum.com/showthread...hreadid=557492

--

Dave Peterson