Store #VALUE! #NUM! #REF! in variable.

Question

So a simple version of what I'm trying to do. Say I know there is an error in cell(1,1), furthermore I know it is either #num!, #ref! or #value!, I want to be able to store the respective error message in a variable, so I can print it to a different sheet. This is what I tried and it clearly failed.

Sub FindAndPrintErrors
dim Store as string
    If IsError(Range("A1"))) = True Then
        Store = Range("A1").value 'it breaks here'
    end if 
    range("B1") = Store
end sub

I know I can do this but I wonder if there is a better way.

Sub FindAndPrintErrors2
    dim Store
        If IsError(Range("A1"))) = True Then
            temp = Range("A1").value 'it breaks here'
        if temp = "error 2029" then
            store = "#num!"
        ' and so on'
        end if 
        range("B1") = Store
    end sub

Answer 1

Just make your variable of type Variant. Then you can put in anything that goes in a cell, including error values.

To elaborate a little, your code could look like this:

Public Sub copyFromCellIfError()
    Dim v
    v = [q42]

    If IsError(v) Then
        [z99] = v
    End If
End Sub

That's assuming you want the actual error value copied, and not a string representation of it.

Answer 2

Instead of .value try .Text. This can be stored in your variable.

?cells(1,2).text
#N/A
?cells(1,2).value
Error 2042
?cells(2,2).text
#REF!
?cells(2,2).value
Error 2023