Results 1 to 2 of 2
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Error report (Excel 2000)

    I need to develop a set of tests for cell contents, and then create on a new sheet,a list of the errors found so the user can return and check them . For instance, one thing I need to test is if the cell's value has more than two decimal places. So I would like to list on the report the cell address and error type "Has a 3-digit decimal" or something. Would this macro be be structured as one that creates the sheet, then goes to A2 (a standard set of column headers CELLADDRESS and TYPE OF ERROR would be in R1 columns A and B )and keeps testing one cell after another for that particular error, and if found, would write the .cell address property to A2 and some error message as above for column B, then drop down one line and wait for the next true test to write again? Is this the correct way to build such a test?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Error report (Excel 2000)

    Sounds reasonable in principle.

    The "logic" for checking for >2 digits is going to be tough since excel keeps EVERYTHING to 15 digits precision. the number 1 has 14 decimals (all zeroes). The easiest test would be to see if the (100 * value) > Int(100*value) (if they are equal it has 2 decimals). Rounding error could creep into this so you might want to ROUND the values before you test them.

    Also how do you ALLOW the number 1.000 to be entered. It has 3 decimals, but excel will calc by most methods that has NO decimals. 1= 1.0 = 1.00 = 1.000 to excel even though (to me at least) there is a world of difference in the PRECISION implied: 1 = +/- 0.5 1.0 has +/- 0.05, etc

    Excel does not however keep track of these significant figures.

    Steve

Posting Permissions

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