PDA

View Full Version : Solved: Replace error types with string



kilbey1
06-21-2007, 11:20 AM
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?


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

qff
06-21-2007, 12:59 PM
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
06-21-2007, 01:41 PM
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!


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