Searching for a number

[goofy78270]

I am looking to search quickly for a number within a cell.

Using a macro, I am looking to search something like:

1,2,3,4-12,45,67,200

to see if the number 11 is contained.

I have recently wrote a macro that splits out the field into an array based on the "," and then again on the "-" while filling in the missing numbers but this is pretty time consuming. Is there an easier way to simply search for the number in question?

 

[marka87uk]

Could you maybe use =FIND(",11", A1) ?

 

[goofy78270]

I am looking to do this in a macro and find does not seem to work for the 4-12 in finding 11.

 

[marka87uk]

How about INSTR? It seemed to work when I tried it using your example...

If InStr(Range("A1"), "11") = 0 Then
MsgBox "No match."
Else
MsgBox "Found."
End If

 

[Scott Huish]

I think the op is trying to find the 11 in 4-12, as this number range contains 4, 5, 6, 7, 8, 9, 10, 11, and 12.

 

[Scott Huish]

Perhaps:

Public Function FindInRange(r As String, srchNum As Long) As Boolean
Dim t, u, v
t = Split(r, ",")
For Each v In t
    u = Split(v, "-")
        If UBound(u) = 0 Then
                If u(0) = srchNum Then
                    FindInRange = True
                    Exit For
                End If
            Else
                If srchNum >= Val(u(0)) And Val(u(1)) >= srchNum Then
                    FindInRange = True
                    Exit For
                End If
        End If
Next
End Function

Usage is =FindInRange(cell, number to look for)

Book1
	A	B	C	D
1	1,2,3,4-12,45,67,200	TRUE
2	1, 3-10,47	FALSE
Sheet1