如何使用VBA在excel中获取错误单元中的底层值

I have this issue to solve purely by VBA, no manual intervention allowed.

我的问题完全由VBA解决，不允许人工干预。

In excel, if one puts a non-numerical string starting with minus sign, e.g "-SomeData", the cell will show as "#NAME?". However, if one selects this cell, in the formula bar it will show "=-SomeData".

在excel中，如果一个非数值字符串以负号e开头。g“-SomeData”，单元格将显示为“#NAME?”但是，如果选择这个单元格，在公式栏中它将显示“=-SomeData”。

Now I need to extract "-SomeData" and paste it on another sheet. I tried .value but it returned error, and .text but it returned "#NAME?".

现在我需要提取“-SomeData”并粘贴到另一个表上。我尝试了。value，但是它返回了错误和。text，但是它返回了#NAME?

I browsed through the entire list for Range object on MSDN without finding a viable solution.

我浏览了MSDN上的Range对象的整个列表，没有找到一个可行的解决方案。

Is there a way to do this purely by VBA? Asking because the user of the macro refused to do manual formatting for the error cells.

有没有一种方法可以完全通过VBA来实现?因为宏的用户拒绝为错误单元进行手动格式化。

Thanks in advance.

提前谢谢。

1 个解决方案

#1

Assuming the error value is in the active cell, do something like:

假设错误值在活动单元格中，请执行以下操作:

Worksheets(1).Range("A1") = "'" & Activecell.Formula