+ Reply to Thread
Results 1 to 3 of 3

#N/A in 150 WS

  1. #1
    Registered User
    Join Date
    05-26-2004
    Posts
    61

    #N/A in 150 WS

    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.

  2. #2
    Chip Pearson
    Guest

    Re: #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" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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. #3
    Dave Peterson
    Guest

    Re: #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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1