Results 1 to 2 of 2
Thread: Error report (Excel 2000)
2003-08-10, 15:26 #1
- Join Date
- May 2001
- Philadelphia, Pennsylvania
- 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?
2003-08-10, 17:19 #2
- Join Date
- Jul 2002
- Pittsburgh, Pennsylvania, USA
- 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.