VBA find in string

  • Thread starter Thread starter Christopher A. Kelly
  • Start date Start date
C

Christopher A. Kelly

I'm trying to determine what type of park the different National Parks are
using the following function. obvisously i'm doing something wrong. in the
formula bar i entered =CalcValue(c2) but it returns #value.


Module1->
Function CalcValue(MyCell as String) As String

If InStr(0, MyCell, "National Park") Then
CalcValue = "NP"
ElseIf InStr(0, MyCell, "Historic") Then
CalcValue = "HP"
ElseIf InStr(0, MyCell, "Military") Then
CalcValue = "MP"
ElseIf InStr(0, MyCell, "Preserve") Then
CalcValue = "NPRE"
ElseIf InStr(0, MyCell, "Monument") Then
CalcValue = "NM"
ElseIf InStr(0, MyCell, "Memorial") Then
CalcValue = "NM"
ElseIf InStr(0, MyCell, "Senic") Then
CalcValue = "NS"
ElseIf InStr(0, MyCell, "Recreation") Then
CalcValue = "NR"
Else
CalcValue = "0"
End If

End Function
 
Christopher,

The Start position must be a positive number and you
are using 0. Also, you might prefer the Select case structure
better than a series of Ifs...
'--------------------------
Function CalcValue(MyCell As String) As String
Select Case True
Case InStr(1, MyCell, "National Park", 1)
CalcValue = "NP"
Case InStr(1, MyCell, "Historic", 1)
CalcValue = "HP"
Case InStr(1, MyCell, "Military", 1)
CalcValue = "MP"
Case InStr(1, MyCell, "Preserve", 1)
CalcValue = "NPRE"
Case InStr(1, MyCell, "Monument", 1)
CalcValue = "NM"
Case InStr(1, MyCell, "Memorial", 1)
CalcValue = "NM"
Case InStr(1, MyCell, "Senic", 1)
CalcValue = "NS"
Case InStr(1, MyCell, "Recreation", 1)
CalcValue = "NR"
Case Else
CalcValue = "0"
End Select
End Function
'----------------------------------

Regards,
Jim Cone
San Francisco, USA


message I'm trying to determine what type of park the different National Parks are
using the following function. obvisously i'm doing something wrong. in the
formula bar i entered =CalcValue(c2) but it returns #value.
Module1->
Function CalcValue(MyCell as String) As String

If InStr(0, MyCell, "National Park") Then
CalcValue = "NP"
ElseIf InStr(0, MyCell, "Historic") Then
CalcValue = "HP"
ElseIf InStr(0, MyCell, "Military") Then
CalcValue = "MP"
ElseIf InStr(0, MyCell, "Preserve") Then
CalcValue = "NPRE"
ElseIf InStr(0, MyCell, "Monument") Then
CalcValue = "NM"
ElseIf InStr(0, MyCell, "Memorial") Then
CalcValue = "NM"
ElseIf InStr(0, MyCell, "Senic") Then
CalcValue = "NS"
ElseIf InStr(0, MyCell, "Recreation") Then
CalcValue = "NR"
Else
CalcValue = "0"
End If
End Function
 
Thank you that fixed it, I haven't used VBA in a couple of years and it is
obvious that I have forgotten alot.

Thank You again Jim
Christopher,

The Start position must be a positive number and you
are using 0. Also, you might prefer the Select case structure
better than a series of Ifs...
'--------------------------
Function CalcValue(MyCell As String) As String
Select Case True
Case InStr(1, MyCell, "National Park", 1)
CalcValue = "NP"
Case InStr(1, MyCell, "Historic", 1)
CalcValue = "HP"
Case InStr(1, MyCell, "Military", 1)
CalcValue = "MP"
Case InStr(1, MyCell, "Preserve", 1)
CalcValue = "NPRE"
Case InStr(1, MyCell, "Monument", 1)
CalcValue = "NM"
Case InStr(1, MyCell, "Memorial", 1)
CalcValue = "NM"
Case InStr(1, MyCell, "Senic", 1)
CalcValue = "NS"
Case InStr(1, MyCell, "Recreation", 1)
CalcValue = "NR"
Case Else
CalcValue = "0"
End Select
End Function
'----------------------------------

Regards,
Jim Cone
San Francisco, USA


message I'm trying to determine what type of park the different National Parks are
using the following function. obvisously i'm doing something wrong. in the
formula bar i entered =CalcValue(c2) but it returns #value.
Module1->
Function CalcValue(MyCell as String) As String

If InStr(0, MyCell, "National Park") Then
CalcValue = "NP"
ElseIf InStr(0, MyCell, "Historic") Then
CalcValue = "HP"
ElseIf InStr(0, MyCell, "Military") Then
CalcValue = "MP"
ElseIf InStr(0, MyCell, "Preserve") Then
CalcValue = "NPRE"
ElseIf InStr(0, MyCell, "Monument") Then
CalcValue = "NM"
ElseIf InStr(0, MyCell, "Memorial") Then
CalcValue = "NM"
ElseIf InStr(0, MyCell, "Senic") Then
CalcValue = "NS"
ElseIf InStr(0, MyCell, "Recreation") Then
CalcValue = "NR"
Else
CalcValue = "0"
End If
End Function
 
Jim Cone said:
The Start position must be a positive number and you
are using 0. Also, you might prefer the Select case structure
better than a series of Ifs...
Click to expand...
....

OP should note that both monuments and memorials return "NM". If
intentional, clearer to code it as an OR condition. Also, shouldn't "Senic"
be "Scenic"?

That said, this could be done in worksheet formulas. Given the 2-column
named range TBL containing

* 0
*Historic* HP
*Memorial* NME
*Military* MP
*Monument* NMO
*National Park* NP
*Preserve* NPRE
*Recreation* NR
*Scenic* NS

the desired entry in the right hand column corresponding to an entry x would
be given by

=LOOKUP(1,MATCH(INDEX(TBL,0,1),x,0),INDEX(TBL,0,2))

Hardcoding would make this even shorter. If TBL were in A1:B9, the formula
could be reduced to

=LOOKUP(1,MATCH($A$1:$A$9,x,0),B$1:B$9)
 
Back
Top