+ Reply to Thread
Results 1 to 2 of 2

#Value! errors

  1. #1
    Registered User
    Join Date
    02-07-2006
    Posts
    1

    #Value! errors

    I have experienced a problem that I am unable to find a rectification for. When my staff constantly use the space bar to delete an entered in error number and leave the cell blank (although there is a space in the cell) any cell which uses that cell within its formulation shows #Value! or #####. I am aware that they should not be using the space bar to delete numbers and the space within the cell is the reason for the other formulation errors. I guess my question is how do I format the cell so that only a number and nothing else can be entered?

  2. #2
    Dave Peterson
    Guest

    Re: #Value! errors

    Maybe you can use Data|Validation to stop most problems.

    But data|validation can be broken by copy|pasting.

    You may want to consider modifying your formulas (yech!) to something like:

    =N(A1)+N(A5)+N(A7)

    (or use =sum(). It'll ignore text.)

    Or use a worksheet event that looks for this kinds of changes.

    If you want to try...

    rightclick on the worksheet tab that should have this behavior. Select view
    code and paste this in:

    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myCell As Range
    On Error Resume Next 'just fly by any errors
    For Each myCell In Target.Cells
    If myCell.HasFormula = False Then
    If IsEmpty(myCell.Value) = False Then
    If Trim(myCell.Value) = "" Then
    Application.EnableEvents = False
    myCell.ClearContents
    Application.EnableEvents = True
    End If
    End If
    End If
    Next myCell
    On Error Goto 0
    End Sub

    One of the things that I don't like about this kind of event macro is that it
    kills the Edit|Undo stack. If you can live with that problem, then maybe it's
    worth a shot.



    Field8585 wrote:
    >
    > I have experienced a problem that I am unable to find a rectification
    > for. When my staff constantly use the space bar to delete an entered in
    > error number and leave the cell blank (although there is a space in the
    > cell) any cell which uses that cell within its formulation shows
    > #Value! or #####. I am aware that they should not be using the space
    > bar to delete numbers and the space within the cell is the reason for
    > the other formulation errors. I guess my question is how do I format
    > the cell so that only a number and nothing else can be entered?
    >
    > --
    > Field8585
    > ------------------------------------------------------------------------
    > Field8585's Profile: http://www.excelforum.com/member.php...o&userid=31282
    > View this thread: http://www.excelforum.com/showthread...hreadid=509519


    --

    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