relative reference

N

nix2004

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
 
B

Bob Phillips

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)
 
N

nix2004

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
 
N

nix2004

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
 
Top