Excel range usage question (cell error checking)

Question

I have the following error for every cell: "the number in this cell is formatted as text or preceded by an apostrophe" To duplicate the error: I have to store this results that come from a Web services as strings array: Sample: (3 by 3 array)

$402,568.03 26.2% 30,052

$107,719.59 7.0% 55,176

$81,370.35 5.3% 54,365

let values be the string array holding sample (string[,] values) Let r be the range to target (Excel.Range r) If I do: r.Value2 = values;

I would like to know how to get rid of it. Any .net code will be great (C# or VB.NET)

Note: 1) Looping in every cell is not an option as the array can be quite big (100, 54) or (1024, 104). I have done and it does not have this error but the performance is very bad.

2) Disable the errors is not an option, because I do not have control over the client settings.

3) I notice that I use the conver to number context menu fix the issue, but it is possible to do it for the affeccted range programmatically.

Answer 1

Hi,

you can programmatically ignore errors on a per Cell basis. This can be accomplished by the following vba code:

<tableObject>.Range("Q1:Q1000").Errors.Item(<errorNo>).Ignore = true

where <tableObject> is the table your data is located and <errorNo> is one of the following error categories:

1 xlEvaluateToError
2 xlTextDate
3 xlNumberAsText
4 xlInconsistentFormula
5 xlOmittedCells
6 xlUnlockedFormulaCells
7 xlEmptyCellReferences

of course you can loop to disable all 7 error types

edit:

what about adding a subroutine to your excel-file in the first place using the above code and loops and calling this subroutine from vsto:

ThisWorkbook.Application.Run("Sheet1.SayHelloVBA")

does this work?