#N/A in 150 WS

[halem2]

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

 

[Chip Pearson]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[Dave Peterson]

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.