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.

Phil Rykoff · Accepted Answer · 2010-03-11 12:59:17Z

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?

edited Mar 11, 2010 at 12:59

answered Mar 4, 2010 at 23:09

Phil Rykoff

12.1k3 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mas_oz2k1 Over a year ago

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.

Phil Rykoff Over a year ago

what about calling a subroutine doing the loops as suggested?

mas_oz2k1 Over a year ago

looping in every cell does not need this construct as it works ok, I am looking for a range solution not a cell solution

Collectives™ on Stack Overflow

Excel range usage question (cell error checking)

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related