Hi all,
I am a beginner in VBA, and I need a function that return 1 or 0
depending if my string contains one of the US states.

To explain a bit more :

If my cell = "East Syracuse, NY"
I want to return 1 because there is a US state in the string.
If my cell = Karlsruhe, Germany
I want to return 0


I have a list of all the US states (2 letter code).
For the function, I don't mind if it is a big "if then" with all the
50 conditions (number of US states).

I am aware of the FIND function in Excel, which does what I need, but
the formula is too big (I need to compare with the 50 US states), it's
why I need a macro. And FIND is not a vba function...
One more thing : it has to be case sensitive.

Any help would be appreciated.

Thanks in advance,
Yann

since you have a list how about a lookup or match
=if(vlookup(right(a1,2),lookuptable,1),1,"")

--
Don Guillett
SalesAid Software

"bibi-phoque" wrote in message
om...
Hi all,
I am a beginner in VBA, and I need a function that return 1 or 0
depending if my string contains one of the US states.

To explain a bit more :

If my cell = "East Syracuse, NY"
I want to return 1 because there is a US state in the string.
If my cell = Karlsruhe, Germany
I want to return 0


I have a list of all the US states (2 letter code).
For the function, I don't mind if it is a big "if then" with all the
50 conditions (number of US states).

I am aware of the FIND function in Excel, which does what I need, but
the formula is too big (I need to compare with the 50 US states), it's
why I need a macro. And FIND is not a vba function...
One more thing : it has to be case sensitive.

Any help would be appreciated.

Thanks in advance,
Yann

Something like this:

Public Function ContainState(rng As Range)
Dim vArr As Variant
Dim i As Long
Dim sStr As String
Dim bFound As Boolean
vArr = Array("AL","AK", ... )
sStr = rng(1).Text
For i = LBound(vArr) To UBound(vArr)
If InStr(1, sStr, " " & vArr(i), vbBinaryCompare) 0 Then
bFound = True
Exit For
End If
Next
If bFound Then
ContainState = 1
Else
ContainState = 0
End If
End Function

--
Regards,
Tom Ogilvy

"bibi-phoque" wrote in message
om...
Hi all,
I am a beginner in VBA, and I need a function that return 1 or 0
depending if my string contains one of the US states.

To explain a bit more :

If my cell = "East Syracuse, NY"
I want to return 1 because there is a US state in the string.
If my cell = Karlsruhe, Germany
I want to return 0


I have a list of all the US states (2 letter code).
For the function, I don't mind if it is a big "if then" with all the
50 conditions (number of US states).

I am aware of the FIND function in Excel, which does what I need, but
the formula is too big (I need to compare with the 50 US states), it's
why I need a macro. And FIND is not a vba function...
One more thing : it has to be case sensitive.

Any help would be appreciated.

Thanks in advance,
Yann

Hi,
This I think will work, but you will have to expand the case to cover all of
the state codes. It puts a 1 or 0 in ReturnValue, but it does not do anything
with it?:

Sub USCity()
TwoRight = Right(ActiveCell.Value, 2)
ThreeRight = Right(ActiveCell.Value, 3)
Left1 = Left(ThreeRight, 1)
If Left1 = " " Then
Select Case TwoRight
Case "NY"
ReturnValue = 1
End Select

Else
ReturnValue = 0
End If

End Sub

"bibi-phoque" wrote:

Hi all,
I am a beginner in VBA, and I need a function that return 1 or 0
depending if my string contains one of the US states.

To explain a bit more :

If my cell = "East Syracuse, NY"
I want to return 1 because there is a US state in the string.
If my cell = Karlsruhe, Germany
I want to return 0


I have a list of all the US states (2 letter code).
For the function, I don't mind if it is a big "if then" with all the
50 conditions (number of US states).

I am aware of the FIND function in Excel, which does what I need, but
the formula is too big (I need to compare with the 50 US states), it's
why I need a macro. And FIND is not a vba function...
One more thing : it has to be case sensitive.

Any help would be appreciated.

Thanks in advance,
Yann

Add this UDF

Function StringFound(val, aryValues)
Dim i As Long
Dim aryTmp
If TypeName(aryValues) = "Range" Or _
VarType(aryValues) vbArray Then
aryTmp = aryValues
Else
StringFound = "Error in lookup values"
Exit Function
End If
For i = LBound(aryTmp) To UBound(aryTmp)
If val Like "*" & aryValues(i, 1) & "*" Then
StringFound = 1
Exit Function
End If
Next i
StringFound = 0
End Function


and use like so

1) Create a range of state ids, named say States, and a formula of

=IF(stringfound("This state NM",States),1,0)

2) Include the states in the formula

=IF(stringfound("This state NM",{"AL";"AZ";"CA";"NM"}),1,0)

--

HTH

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


"bibi-phoque" wrote in message
om...
Hi all,
I am a beginner in VBA, and I need a function that return 1 or 0
depending if my string contains one of the US states.

To explain a bit more :

If my cell = "East Syracuse, NY"
I want to return 1 because there is a US state in the string.
If my cell = Karlsruhe, Germany
I want to return 0


I have a list of all the US states (2 letter code).
For the function, I don't mind if it is a big "if then" with all the
50 conditions (number of US states).

I am aware of the FIND function in Excel, which does what I need, but
the formula is too big (I need to compare with the 50 US states), it's
why I need a macro. And FIND is not a vba function...
One more thing : it has to be case sensitive.

Any help would be appreciated.

Thanks in advance,
Yann

Thanks a lot!
This is working perfectly.

Appreciate your help.

Regards,
Yann

*** Sent via Developersdex http://www.developersdex.com ***