Custom Function If..Else and reading text strings
 

I'm trying to write a function to read a string and depending on the
contents of that string assign a type to it. The code below is just an
example. I would use the IF function built-in to Excel but I have 15+
types and couple different ways to determine some of those types. This
is my first function.

When I use the following function, all I get is a '0' in the cell, not
even my error message. That makes me think that I'm using the StrComp
function incorrectly.

For sample data I'm using:
< 5KOLRAWAL1BI
< 2USSTVRVS1GI
< 5KOLTVWAL1BI

Function ReadAdCode(strAdCode As String)
Dim strAdType As String

If StrComp(strAdCode, "*5KOL*", vbTextCompare) = 0 Then
strAdType = "OTHER MEDIA"
ElseIf StrComp(strAdCode, "*TV*", vbTextCompare) = 0 Then
strAdType = "TVA"
Else
strAdType = "This is Not Working"
End If
End Function

Any help is greatly appreciated!

Custom Function If..Else and reading text strings
 

You need to return the value, not just assign it to a variable

Function ReadAdCode(strAdCode As String)
Dim strAdType As String

If StrComp(strAdCode, "*5KOL*", vbTextCompare) = 0 Then
ReadAdCode = "OTHER MEDIA"
ElseIf StrComp(strAdCode, "*TV*", vbTextCompare) = 0 Then
ReadAdCode = "TVA"
Else
ReadAdCode = "This is Not Working"
End If
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dan" wrote in message
oups.com...
I'm trying to write a function to read a string and depending on the
contents of that string assign a type to it. The code below is just an
example. I would use the IF function built-in to Excel but I have 15+
types and couple different ways to determine some of those types. This
is my first function.

When I use the following function, all I get is a '0' in the cell, not
even my error message. That makes me think that I'm using the StrComp
function incorrectly.

For sample data I'm using:
< 5KOLRAWAL1BI
< 2USSTVRVS1GI
< 5KOLTVWAL1BI

Function ReadAdCode(strAdCode As String)
Dim strAdType As String

If StrComp(strAdCode, "*5KOL*", vbTextCompare) = 0 Then
strAdType = "OTHER MEDIA"
ElseIf StrComp(strAdCode, "*TV*", vbTextCompare) = 0 Then
strAdType = "TVA"
Else
strAdType = "This is Not Working"
End If
End Function

Any help is greatly appreciated!

Custom Function If..Else and reading text strings
 

You have to assign the result to ReadAdCode, e.g....

Public Function ReadAdCode(strAdCode As String) As String
Dim strAdType As String
If StrComp(strAdCode, "*5KOL*", vbTextCompare) = 0 Then
strAdType = "OTHER MEDIA"
ElseIf StrComp(strAdCode, "*TV*", vbTextCompare) = 0 Then
strAdType = "TVA"
Else
strAdType = "This is Not Working"
End If
ReadAdCode = strAdType
End Function

Note that StrComp compares strings ASCII values - so * is not a
wildcard. I think you're really after something like

Public Function ReadAdCode(strAdCode As String) As String
Dim strAdType As String
If InStr(1, strAdCode, "5KOL", vbTextCompare) Then
strAdType = "OTHER MEDIA"
ElseIf InStr(1, strAdCode, "TV", vbTextCompare) Then
strAdType = "TVA"
Else
strAdType = "This is Not Working"
End If
ReadAdCode = strAdType
End Function

In article .com,
"Dan" wrote:

I'm trying to write a function to read a string and depending on the
contents of that string assign a type to it. The code below is just an
example. I would use the IF function built-in to Excel but I have 15+
types and couple different ways to determine some of those types. This
is my first function.

When I use the following function, all I get is a '0' in the cell, not
even my error message. That makes me think that I'm using the StrComp
function incorrectly.

For sample data I'm using:
< 5KOLRAWAL1BI
< 2USSTVRVS1GI
< 5KOLTVWAL1BI

Function ReadAdCode(strAdCode As String)
Dim strAdType As String

If StrComp(strAdCode, "*5KOL*", vbTextCompare) = 0 Then
strAdType = "OTHER MEDIA"
ElseIf StrComp(strAdCode, "*TV*", vbTextCompare) = 0 Then
strAdType = "TVA"
Else
strAdType = "This is Not Working"
End If
End Function

Custom Function If..Else and reading text strings
 

I added the comparison " 0" to the if staement and it works. This
should save me about 2.5 hrs/ week. Thanks!

Custom Function If..Else and reading text strings
 

Not strictly necessary, since any non-zero result of InStr will be
interpreted as True, and any zero result as False. But it certainly
doesn't hurt.

In article .com,
"Dan" wrote:

I added the comparison " 0" to the if staement and it works.