Solved: Replace error types with string

[kilbey1]

I have a very simple function set to check a cell from another worksheet. My problem: when it encounters #N/A, I can't get the value of the cell to be replaced with "~". I know #N/A is either error.type(7) or, if undefined, 'other'.

Can someone please point me in the right direction, so I can get any error results displayed as ~ in the cell?

[vba]
Function checkCellInfo(wcell As String)
wcell = Trim(wcell)
If wcell <> "" Then
'cell is not empty
If IsNumeric(wcell) Or Not Error <= 7 Then
checkCellInfo = wcell
ElseIf IsError(wcell) Then
checkCellInfo = "~"
Else
checkCellInfo = "~"
End If
Else
'check to see if there is an error
checkCellInfo = ""
End If
End Function
[/vba]

[qff]

Hi

I think this should do what you want.

Function checkCellInfo(wcell As Variant)
If Not IsError(wcell) Then

If wcell <> "" Then
checkCellInfo = wcell
Else
checkCellInfo = ""
End If

ElseIf wcell = CVErr(2042) Then
checkCellInfo = "~"

End If

End Function

regards
qff

[kilbey1]

Thanks, that seemed to do it. I looked further into CVErr and tweaked it slightly to accomodate for more errors. Thanks for putting me on track!

[VBA]
Function checkCellInfo(wcell As Variant)
If Not IsError(wcell) Then
If wcell <> "" Then
checkCellInfo = wcell
Else
checkCellInfo = ""
End If
Else
errval = wcell.Value
Select Case errval
Case CVErr(xlErrNA)
checkCellInfo = "~"
Case CVErr(xlErrValue)
checkCellInfo = "~"
Case Else
checkCellInfo = ""
End Select
End If
End Function
[/VBA]