Here is a shorter, slightly different approach to do what you want...

Sub Test()
Dim myStr As String
Dim cVal As Long
Dim HowMany As Long
myStr = "1,21,31,1,42"
cVal = "1"
HowMany = UBound(Split(Replace("," & myStr & _
",", " ", ""), "," & cVal & ","))
MsgBox "There are " & HowMany & " occurrences of """ & _
cVal & """ in """ & myStr & """"
End Sub

You can test HowMany in your code if you need to differentiate between none
and some occurrences. Using Dave's If..Then approach, just do this after you
assign the value to the HowMany variable as in my above code...

If HowMany = 0 Then
MsgBox "Not found"
Else
MsgBox "Woohoo! Found it"
End If

--
Rick (MVP - Excel)


"CG Rosen" wrote in message
...
Thanks for helpful input from you all. Dave's answer is the one that
covers my needs the best.

Thank you

cgr

"Dave Peterson" skrev i meddelandet
...
How about:

Option Explicit
Sub testme()

Dim myStr As String
Dim myStrToTest As String
Dim cVal As String 'just to show how
Dim cValToUse As String
Dim myPos As Long
Dim HowMany As Long

myStr = "1,21,31"
cVal = "21"

'remove any possible spaces
myStrToTest = Replace(myStr, " ", "")
'make sure each element has a leading and trailing comma
myStrToTest = "," & myStr & ","

'same thing with cVal
cValToUse = "," & cVal & ","

'now look for that modified value in the modified string
myPos = InStr(1, myStrToTest, cValToUse, vbTextCompare)

If myPos = 0 Then
MsgBox "not found"
Else
MsgBox "woohoo! Found it"
End If

'if the strings we
mystrtotest = ",1,21,31,21,"
cValToUse = ",21,"
'(after modifying them)
'and you wanted to count how many times 21 appeared:

HowMany = (Len(myStrToTest) - Len(Replace(myStrToTest, cValToUse,
""))) _
/ Len(cValToUse)
MsgBox HowMany

End Sub

CG Rosen wrote:

Good day group,

Trying to use the InStr function in VBA to find if various numbers is
contained in a string.

If InStr(c.value, "String") 0
Then..........................................etc

The c.value can be from 1 to 50. The string can look like "1,15,21" . If
the
c.value =1 that number
is then found 3 times. How is it possible to do a distinction that 1 is
only
found one time and 2 one time and say 21 one time and so on? Do I need
some
other kind of separator in the string?

Hope my question is understandable.

Brgds

cgr

--

Dave Peterson