Excel的使用范围问题(电池错误检查) [英] Excel range usage question (cell error checking)
问题描述
我对每一个细胞出现以下错误:
这个小区的编号的格式为撇号pceded文本或$ P $
要复制的错误:
我不得不存储这个结果来自于Web服务作为字符串数组:
样品:(3×3阵列)
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%30052
$402,568.03 26.2% 30,052
$ 107,719.59 7.0%55176
$107,719.59 7.0% 55,176
$ 81,370.35 5.3%54365
$81,370.35 5.3% 54,365
让值是字符串数组拿着样品(字符串[,]值)
设r是范围目标(Excel.Range R)
如果我做:
r.Value2 =值;
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;
我想知道如何摆脱它。任何.NET code将是巨大的(C#或VB.NET)
I would like to know how to get rid of it. Any .net code will be great (C# or VB.NET)
请注意:
1)在每一个细胞循环不像阵列可以是相当大的一个选项(100,54)或(1024,104)。我已经做了,它没有这个错误,但表现很糟糕。
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)禁用错误是不是一种选择,因为我没有在客户端设置控制。
2) Disable the errors is not an option, because I do not have control over the client settings.
3)我注意到,我用CONVER到号码上下文菜单解决该问题,但它有可能做到这一点的affeccted范围内编程。
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.
推荐答案
您可以通过编程忽略每个小区的错误。这可以通过下面的VBA code来完成:
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
其中,&LT; tableObject&GT;
是您的数据所在的表和&LT; errorNo&GT;
是一以下错误类别:
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
当然
您可以循环禁用所有7错误类型
of course you can loop to disable all 7 error types
编辑:
怎么样使用上述code和循环摆在首位加入子程序您的Excel文件,并从VSTO调用这个子程序:
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")
这会起作用?
这篇关于Excel的使用范围问题(电池错误检查)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!