Reading value of formula from vba.

Question

In first step of my project I filled up the table with proper formatted formulas using VBA loop. Next step in VBA is to analyze, in another loop, the value of these formulas. I tried to use e.g. str = Range("A1").Value (str is a String) but run time error #13: Type mismatch is displayed. In Watches for Range("A1").Value exists Value: Error2042, Type: Variant/Error. I cannot use Range("A1").Formula because I want to read calculated value of this formula. Is it any possibility to this? Could you help me, please?

One of the ways to bypass this problem is to copy cells, paste special (values only) and analyze values. My approach is to keep formulas "life" to see changes in other sheets.

Krzysztof

Answer

Str is a string, so it can't equal an error value. If cell A1 is an error, then what do you want the variable str to be? Let's say you want str to equal "err" if A1 has an error value, and otherwise to equal the value in A1. The code is:

Dim str as string if iserror(Range("A1").Value) then

    str = "err"

else

    str = Range("A1").Value

endif