Hi,

wondering if anyone can help me with this formula.

i have one cell in worksheet. i want to extract a word (text) and i
the next column based on "text" i want to assign a description to it.
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

ni

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

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/

thanks for your response!

i am still getting 0 in the cell value. i will try to explain i
detail.

Column A
VOY-CCC
Xyz-alpha from Eastern
tera-abc morgan
etc.

i want to set up column B based on values from column A. i want t
define if it's VOY then it should be xoyager1, if it's xyz or anythin
else, it should be based on them. coumn A could be 15 lines, al
different product group, or could be 5 or 6. y is based on descriptio
from column A.

hope this helps.

ni

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

why would it return value 0. is it cause not recognizing formula or ..
i tried following and it returns wrong value. not zero though.

If Evaluate("IsError(Find(""" & ENC & """, x))") Then
ProductID = "ENC"
Else
ProductID = "Voyager"
End I

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