Excel range usage issue (cell error checking)

Question

I have the following error for each cell: "the number in this cell is formatted as text or precedes the apostrophe" To duplicate the error: I have to save these results that come from Web services as an array of strings: Example: (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 a string array containing a sample (string values ​​[,]) Let r be the target range (Excel.Range r) If I do: r.Value2 = values;

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

Note: 1) The loop in each cell is not an option, since the array can be quite large (100, 54) or (1024, 104). I did, and this error does not, but the performance is very poor.

2) Disabling errors is not possible, because I do not have control over the client settings.

3) I notice that I use the context menu to convert to numbers, fix the problem, but this is possible for software software.

Answer 1

you can programmatically ignore errors based on each cell. This can be done using the following vba code:

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

where <tableObject> is the table in which 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 cyclically disable all 7 types of errors

edit:

how about adding a subroutine to your excel file primarily using the above code and loops and calling that subroutine from vsto:

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

does it work