VBA function : How to search a string in another string?
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 |
VBA function : How to search a string in another string?
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 |
VBA function : How to search a string in another string?
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 |
VBA function : How to search a string in another string?
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 |
VBA function : How to search a string in another string?
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 |
VBA function : How to search a string in another string?
Thanks a lot!
This is working perfectly. Appreciate your help. Regards, Yann *** Sent via Developersdex http://www.developersdex.com *** |
All times are GMT +1. The time now is 06:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com