4

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.

mas_oz2k1
  • 2,851
  • 3
  • 34
  • 41

1 Answers1

3

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?

Phil Rykoff
  • 11,999
  • 3
  • 39
  • 63
  • thanks, but your solution does not work for multi-cell and unfortunately I need a multicell solution because my app is a vsto app and any COM calls are expensive. – mas_oz2k1 Mar 09 '10 at 00:24
  • what about calling a subroutine doing the loops as suggested? – Phil Rykoff Mar 11 '10 at 12:59
  • looping in every cell does not need this construct as it works ok, I am looking for a range solution not a cell solution – mas_oz2k1 Mar 13 '10 at 21:39