Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
#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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
#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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
#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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|