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

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

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

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!