Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error indicators in cells
In the KB article Q291047 it is stated:
Method 1: Use the Error Button If the cells in which numbers are displayed as text contain an error indicator in the upper-left corner, follow these steps: Click the cell that contains the error indicator. Click the error button next to the cell, and then click Convert to Number on the shortcut menu How can I do the same thing through VBA? Thanks Trevor |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error indicators in cells
Assuming that Range A1 has the numeric value of "1" and you put in a text
number in Range A2, then run the following snippet: If Range("A2").Errors.Item(xlNumberAsText).Value = True Then intResponse = MsgBox("Cell A2 has a number as text.",68,"Number as Text") If intResponse = 6 Then Range("A1").Copy Range("A2").PasteSpecial(xlPasteValues,xlPasteSpec ialOperationMultiply,False ,False) End If Else MsgBox "Cell A1 is a number." End If Or if you prefer to just apply the copy and paste to a whole range, you can do that too without necessarily having to check to see of the range is numeric or text format of numbers, which is the method I had to use back in the XL97 days. Main reason why I had to use this method back then, I tended to use string functions for bringing down some of the information to reduce the amount of data entry, but at the same time, it converted the numbers to values, which didn't sort too well, so I had to use the above method to be able to sort properly. -- Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Trevor" wrote in message ... In the KB article Q291047 it is stated: Method 1: Use the Error Button If the cells in which numbers are displayed as text contain an error indicator in the upper-left corner, follow these steps: Click the cell that contains the error indicator. Click the error button next to the cell, and then click Convert to Number on the shortcut menu How can I do the same thing through VBA? Thanks Trevor |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error indicators in cells
See one more response to the original thread.
Trevor wrote: In the KB article Q291047 it is stated: Method 1: Use the Error Button If the cells in which numbers are displayed as text contain an error indicator in the upper-left corner, follow these steps: Click the cell that contains the error indicator. Click the error button next to the cell, and then click Convert to Number on the shortcut menu How can I do the same thing through VBA? Thanks Trevor -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error indicators in cells
Here's what the help file shows for the Item Property of the Errors Object:
Returns a single member of the Error object. expression.Item(Index) expression Required. An expression that returns an Errors object. Index Required Variant. The Index can also be one these constants. xlEvaluateToError The cell evaluates to an error value. xlTextDate The cell contains a text date with 2 digit years. xlNumberAsText The cell contains a number stored as text. xlInconsistentFormula The cell contains an inconsistent formula for a region. xlOmittedCells The cell contains a formula omitting a cell for a region. xlUnlockedFormulaCells The cell which is unlocked contains a formula. xlEmptyCellReferences The cell contains a formula referring to empty cells. The above constants returns the values of 1 to 7 from top to bottom in the order as shown above. Therefore, if you want to setup a function that returns which errors if any, you can setup a For...Next looping pattern like the following: Function fncErrorCheck(Rng as Range) as String Dim cell as Range, I as Long, lngCErrFound fncErrorCheck = "" For Each cell in Range lngCErrFound = 0 For I = 1 to 7 Step 1 If cell.Errors.Item(I).Value = True Then If lngCErrFound Then fncErrorCheck = fncErrorCheck & ";" & cell.Address(True,True,xlA1,True) _ & "-" & I lngCErrFound = -1 Else fncErrorCheck = fncErrorCheck & "," & I End If End If Next I Next If fncErrorCheck < "" Then fncErrorCheck = VBA.Strings.Mid(fncErrorCheck,2) End If End Function Note: The Mid function in the Strings class of the VBA library does not require the length to be entered unlike the Mid Function on the spreadsheet side on the XLXP version. I am not sure why MS changed that particular argument on the spreadsheet side to be a required argument, but none the less, they did. -- Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Trevor Fernandes" wrote in message ... That works fine with "numbers stored as text". But how do I check for the other rules like "inconsistent formula in region" or "formula omits cells in region"? Thanks *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comments Without Indicators; Displayed when Cells are Clicked | Excel Discussion (Misc queries) | |||
Hiding error indicators | Excel Discussion (Misc queries) | |||
Can one comment have two indicators? | Excel Discussion (Misc queries) | |||
Comment Indicators | Excel Discussion (Misc queries) | |||
Error Indicators in Cells | Excel Programming |