Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Validation of a Numbre (XP: SP3)

    I have VBA code that sums up three cells of a spreadsheet. One of the three cells may contain a #REF! (don't ask why) and of course if the code is run an error message is returned: Unable to get the SUM property of the Worksheet Function class.

    Is it possible to trap the error?

    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Validation of a Numbre (XP: SP3)

    You could use code like this:

    Dim dblSum As Double
    On Error Resume Next
    dblSum = dblSum + ActiveCell.Offset(0, 2)
    dblSum = dblSum + ActiveCell.Offset(0, 4)
    dblSum = dblSum + ActiveCell.Offset(0, 7)
    ActiveCell = dblSum

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation of a Numbre (XP: SP3)

    Hans,

    I'll have to read up on the dblSum as Double as I am unfamilar with it. I did however come up with this:


    Regards,
    John

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Validation of a Numbre (XP: SP3)

    The line

    Dim dblSum As Double

    declares a variable named dblSum and tells VBA that it is of type Double, i.e. a double precision floating point number. This is (more or less) the type of number Excel uses for its calculations.

    The code I posted will add all valid numbers, and skip values that are text, errors etc. The result will be placed in the active cell.

    The code you posted will quit as soon as it runs into an error. There is no result or feedback, whether an error occurred or not.

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation of a Numbre (XP: SP3)

    Hans,

    Thank you for the explanation.

    John

Posting Permissions

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