Search code examples
stringexcelvbacell

How to get the underlying value in an error cell in excel using VBA

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

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".

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?".

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

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.

Thanks in advance.

Solution

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

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