IsError and Find are worksheet Functions and you cannot call in that way in
VBA. And you don't restate the selevct value in the Case statement

And where does y get set to a value? Assuming it gets set somewhere, try
this

Function productname()
Dim ProductID As String
Dim x As String
Dim y As String

x = ActiveCell.Offset(0, -1)

Select Case y
Case "xyz"
If Evaluate("IsError(Find(""" & y & """," & x & "))") Then
ProductID = "ENC"
Else
ProductID = "xoyazer"
End If
Case "VOY"
If Evaluate("IsError(Find(""" & y & """," & x & "))") Then
ProductID = "ENC"
Else
ProductID = "xoyager1"
End If
End Select

End Function

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"nix2004 " wrote in message
...
Hi,

wondering if anyone can help me with this formula.

i have one cell in worksheet. i want to extract a word (text) and in
the next column based on "text" i want to assign a description to it. i
have used following, but giving me an error.

Function productname()
Dim ProductID As String
Dim x As String
Dim y As String

x = ActiveCell.Offset(0, -1)

Select Case y
Case y = "xyz"
If (IsError(Find(y, x))) Then
ProductID = "ENC"
Else
ProductID = "xoyazer"
End If
Case y = "VOY"
If (IsError(Find(y, x))) Then
ProductID = "ENC"
Else
ProductID = "xoyager1"
End If
End Select


End Function

thanks

nix


---
Message posted from http://www.ExcelForum.com/