VBA Excel won't ignore "Number Stored in Text" error
I think Excel is getting old.
For life, I have been unable to get an Excel VBA macro to ignore the "Number Stored in Text" error.
In cell C71 in a sheet called "Main" I have a value of 6135413313 which is an Excel alert stored as text. And it should be. But I want to remove that annoying little triangle at the end of my macro.
I have cut my macro code down to bare bones for testing purposes, but this triangle still persists. Here's my macro:
Sub test()
Range("C71").Errors(xlEvaluateToError).Ignore = True
End Sub
How does this not fix this error? I have also tried Range("Main!C71")
. It didn't work either.
It should be incredibly simple, but one line of code still doesn't work. Any ideas?
source to share
you can try this
Sub test()
Sheets("Main").Range("C71").Errors(xlNumberAsText).Ignore = True
End Sub
or
Sub test()
Sheets("Main").Range("C71").Value = Sheets("Main").Range("C71").Value
End Sub
or
Alternatively, you can manually disable background error checking .
you can find this option by clicking File - Excel Options - Formulas and uncheck the box
it will disable error checking for all cells
source to share
Loop through each cell in the range to check for an xlNumberAsText error and set the ignore flag (although if you have a large number of cells this can be slow).
Sub test2()
Call turnOffNumberAsTextError(Sheets("Main").Range("C71"))
End Sub
Sub turnOffNumberAsTextError(rge As Range)
Dim rngCell As Range
For Each rngCell In rge.Cells
With rngCell
If .Errors.Item(xlNumberAsText).Value Then
.Errors.Item(xlNumberAsText).Ignore = True
End If
End With
Next rngCell
End Sub
source to share