CODEWITHSUNDEEP
stringvbaexcel
John W
John W

Reputation: 201

How to check for a variable number within a string

Good Morning,

I am attempting to create VBA code that will identify if a variable value (number) is found within a string. The string can vary in lenght and can contain 1 or more numbers that are sepearted by a , and a space. I have attempted to use the InStr method but unfortunately if my value is 1 and the string contains a 17 it comes back as true. How can I make it so that would return false since 1 is not equal to 17.

Below is my current code:

'FRB_Code and FRB_Array are variable values within my code but for 
'the purpose of this question I have assigned them values.
    FRB_Array = "10, 17, 21" 
    FRB_Code = 1 'ce.Value
    If InStr(FRB_Array, FRB_Code) Then
    MsgBox "Found"
    Else
    MsgBox "Not Found"
    ce.Delete Shift:=xlUp
    End If

    Next ce
    End If

So the end result should be that the FRB_Code was not found in the FRB_Array and there for the cell was deleted.

Thank you for you help.

Upvotes: 0

Views: 167

Answers (4)

Arbra
Arbra

Reputation: 11

You can use REGEX to determine the match.

http://msdn.microsoft.com/en-us/library/twcw2f1c(v=vs.110).aspx

the regex expression would be "1[^\d]|1$" and you would replace 1 with your FB_Code value.

The expression has an or(|) to handle the last number in the array.

Upvotes: 1

Aidan
Aidan

Reputation: 1750

InStr is not really appropriate here because you are comparing numbers rather than strings. To do what you want split the string into pieces and cycle through the returned array checking each item. Val is used to convert each item in the array to an integer.

bFound = False

FRB_Array = "10, 17, 21"
FRB_Code = 17

ar = Split(FRB_Array, ",")

For i = 0 To UBound(ar)

    If FRB_Code = Val(ar(i)) Then
        bFound = True
    End If
Next i

If bFound Then
    MsgBox ("found")
Else
    MsgBox ("not found")
End If

Upvotes: 1

Kapol
Kapol

Reputation: 6463

You can use an array for that.

Sub FindValue()
    Dim sMyString As String
    Dim sToFind As String
    Dim Arr
    Dim i As Long
    Dim bFound As Boolean

    sMyString = "10, 17, 21"
    Arr = Split(sMyString, ", ")
    sToFind = "17"

    For i = 0 To UBound(Arr)
        If Arr(i) = sToFind Then
            MsgBox "Found"
            bFound = True
            Exit For
        End If
    Next i

    If Not bFound Then MsgBox "Not found"
End Sub

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

Problem is that "1" will "instring" to "1", "217","871", etc. Better to pre-pad and post-pad with spaces:

Sub NumberInString()
    BigString = " 1 23 54 79 "
    LittleString = " 23 "
    MsgBox InStr(1, BigString, LittleString)
End Sub

Upvotes: 1

Related Questions