relative reference
 

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

relative reference
 

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/

relative reference
 

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

relative reference
 

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